How to count cells with zeros but not blanks in Excel?
In Excel, it's common to work with datasets that contain a mix of numbers, zeros, and blank cells. For instance, you may have a spreadsheet like the one shown below, where you need to determine exactly how many cells contain a value of zero, while ignoring any blank cells. Accurately counting zero-value cells can be important for data validation, quality control, and various data analysis tasks. If you only count zeros, you can quickly spot issues such as missing entries versus valid zeros in survey results, sales reports, or scientific observations.
Count cells with zeros but non blanks in a range with formula
Count cells with zeros but non blanks in a range with Kutools for Excel
Count cells with exactly zero using VBA code
Count cells with zeros but non blanks in a range with formula
To count the number of cells containing exactly zero in your data range, while ignoring blank cells, you can use a simple formula. This approach is quick and suitable for small- to medium-sized datasets when you need an immediate count and your zeros are represented as numeric values (not as text or formulas resulting in blanks).
Select a blank cell and enter the following formula: =COUNTIF(A1:H8,0) Then press the Enter key. This will return the total number of cells containing a zero, excluding any blank cells in your selected range.
 |
 |
Tip: In this formula, A1:H8 represents your data range. Adjust the range as needed for your worksheet, such as B2:D50 for a different area.
Count cells with zeros but non blanks in a range with Kutools for Excel
Sometimes you may want not only to count zero cells, but also to visually identify and work with them directly. With formulas, it's not possible to select or highlight zero-value cells immediately. However, with Kutools for Excel'sSelect Specific Cells utility, you can both count the number of zeros and easily select or highlight those cells, which is especially useful for further analysis or data formatting.
After free installing Kutools for Excel, proceed with the following steps:
1. Select the range in which you want to count zero cells, then go to Kutools > Select > Select Specific Cells.
2. In the Select Specific Cells dialog, make sure to check the Cell option under Selection type. For Specific type, choose equals and enter 0 in the box next to it. This ensures only numeric zero values are considered.
3. Click OK; a dialog will appear showing the count of zero cells found. Click OK again to close the dialog, and all zero cells remain selected, allowing you to quickly make changes as needed.
 |
Note: To further highlight zero cells, keep them selected and then click Home > Fill Color to apply a color for easy identification.
The Select Specific Cells utility from Kutools for Excel also lets you select cells based on multiple criteria, making it highly versatile for more advanced filtering scenarios.
Click here to free download Kutools for Excel
Count cells with exactly zero using VBA code
If you require a flexible solution for counting zero-value cells in large or dynamically changing datasets, employing a VBA macro can be more efficient. VBA grants you control over complex data ranges, and can be customized to ignore blanks while counting only truly zero values, regardless of cell formatting.
This method is particularly suitable for advanced users or when you need this function repeatedly across multiple workbooks.
1. Go to Developer > Visual Basic to open the Microsoft Visual Basic for Applications window. Click Insert > Module, and then copy and paste the following code into the new module:
Sub CountZeroCells()
Dim rng As Range
Dim cell As Range
Dim zeroCount As Long
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.InputBox("Select the range to count zeros:", xTitleId, Selection.Address, Type:=8)
zeroCount = 0
If Not rng Is Nothing Then
For Each cell In rng
If Not IsEmpty(cell.Value) And cell.Value = 0 Then
zeroCount = zeroCount + 1
End If
Next
MsgBox "Number of zero cells (excluding blanks): " & zeroCount, vbInformation, xTitleId
End If
End Sub2. Run the macro by clicking the 
button. When prompted, select your desired data range in the worksheet. The macro will count and display the number of cells containing precisely zero, ignoring any blank cells.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in