How to count blank or non-blank cells in filtered range in Excel?
When working with large datasets in Excel, it's common to use filters to display only the data you're interested in. After filtering, you might need to further analyze the data by counting how many cells are blank or non-blank in the visible (filtered) results. While Excel offers basic features for counting visible cells, quickly counting only blank or only non-blank cells in a filtered list can be challenging without the right method. Accurately obtaining these counts is important, for example, in data cleaning, summarizing survey responses, or tracking incomplete entries within filtered reports. This article introduces several effective solutions to handle this task, covering both formula-based and VBA-based approaches to meet various practical needs. You’ll also find suggestions for troubleshooting common issues and tips for adapting each solution to specific scenarios.
Count blank cells in filtered range with formula
Count non-blank cells in filtered range with formula
Count blank or non-blank cells in filtered range with VBA code
Count blank cells in filtered range with formula
To count only the blank cells within a filtered range, you can use a combination of the SUBTOTAL function and a helper column. This method is suited for lists that contain filtered data where you want to ignore hidden rows, and you need an accurate count of visible blank entries in a specific column.
Enter this formula in a cell where you want the blank cell count to appear:
=SUBTOTAL(3,A2:A20)-SUBTOTAL(3,B2:B20)
After typing the formula, press the Enter key. The result will display the number of visible blank cells in your filtered list. Refer to the screenshot below for an example:
Explanation and usage tips:
- In this formula,
A2:A20should be a helper column that is guaranteed not to contain any blanks (for instance, a column with sequential numbers or unique identifiers for your rows). B2:B20is the column in which you want to count the blank cells.- The SUBTOTAL(3, range) function returns the count of non-blank, visible cells within the specified range. Here, subtracting the count of non-blank cells in
B2:B20from the total in the helper column gives you the number of blanks in the filtered (visible) data. - This approach only considers cells that remain visible after filters are applied, so it won’t count blank cells in rows hidden by filters.
- Be sure to adjust the ranges (
A2:A20andB2:B20) to match your actual data. The helper column (A) must contain entries in every row—if there are blanks in your helper column, the results may be inaccurate.
Common issues and troubleshooting:
- If the helper column includes hidden or blank values, the blank count will be wrong. Check your helper column for completeness.
- If you add or remove rows, remember to adjust the formula range, otherwise data at the bottom or top may not be included.
- Ensure the filter is actually applied; otherwise, the subtotal will include all rows.
Count non-blank cells in filtered range with formula
To count the number of visible, non-blank cells in a filtered column, Excel provides a straightforward solution using the SUBTOTAL function. This method is ideal if you simply need a count of non-empty entries that remain after a filter is applied, ignoring any hidden rows.
To use this solution, type the following formula in a blank cell where you wish to display the count:
=SUBTOTAL(102,B2:B20)
Then press the Enter key. Excel will immediately output the count of visible, non-blank cells in the specified range. See the example screenshot below:
Explanation and usage tips:
- Here,
B2:B20represents the column you’re analyzing. Adjust this range to fit your dataset. - The
102argument in the SUBTOTAL function ensures only visible cells are counted, and it ignores both hidden rows (from filters) and blanks. - This solution is best for standard filtered lists within a single column.
Precautions:
- This method will not count cells that look “blank” but contain formulas returning an empty string (""), as Excel treats them as not truly empty.
- If you are working with merged cells or irregular data ranges, double-check the formula output for accuracy.
- Remember to update the range if new rows are added or data is moved.
Count blank or non-blank cells in filtered range with VBA code
For more advanced scenarios where your filtered range may include non-contiguous ranges, multiple columns, or where you require a customizable approach, a VBA macro can efficiently loop through only the visible cells and provide an accurate count of blank or non-blank cells. This method is especially suitable if you need flexibility, such as counting blanks/non-blanks across several columns or in complex data layouts that formulas cannot easily handle.
Benefits and applicable scenarios:
- Can work across multiple columns and non-contiguous ranges in a single operation
- Adapts to filters easily—only considers visible cells
- Allows you to choose whether to count blank or non-blank cells in one operation
- Best suited for advanced users comfortable with running macros, and when standard formulas are insufficient
Limitations:
- Requires access to the VBA editor and macro permissions enabled
- The counting logic treats cells containing formula results of "" as blank
1. On the Developer tab, click Visual Basic to open the Microsoft Visual Basic for Applications editor. In the VBA window, click Insert > Module to create a new module. Then copy and paste the code below into the module window:
Sub CountVisibleBlanksOrNonBlanks()
Dim rng As Range
Dim cell As Range
Dim countBlanks As Long
Dim countNonBlanks As Long
Dim resp As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select the filtered range to analyze", xTitleId, rng.Address, Type:=8)
If rng Is Nothing Then Exit Sub
resp = MsgBox("Do you want to count BLANK cells? (Click No to count non-blank cells)", vbYesNo + vbQuestion, xTitleId)
countBlanks = 0
countNonBlanks = 0
For Each cell In rng.SpecialCells(xlCellTypeVisible)
If cell.Value = "" Then
countBlanks = countBlanks + 1
Else
countNonBlanks = countNonBlanks + 1
End If
Next cell
If resp = vbYes Then
MsgBox "Number of visible blank cells: " & countBlanks, vbInformation, xTitleId
Else
MsgBox "Number of visible non-blank cells: " & countNonBlanks, vbInformation, xTitleId
End If
End Sub2. Press F5 to run the code.
- A prompt will appear asking you to select or confirm your target range.
- The macro will ask if you want to count blank cells (click Yes), otherwise click No to count non-blank cells.
- The result will appear in a message box indicating the count of blank or non-blank visible cells.
Operation tips and error handling:
- If your range includes merged cells, the macro will still count correctly, but be aware of any overlaps or misalignments in your filtered data.
- If you attempt to run the macro while no range is selected, you’ll be prompted to select a valid range.
- For large datasets, the macro may take a few seconds to complete. Wait for the result message box.
- If you see an error or dialog about 'No cells found,' ensure your selection includes at least one visible row and that the filter is active.
Summary suggestions: For day-to-day use, Excel's formula methods are simple, quick, and require no extra setup. If you encounter situations with multiple columns, non-contiguous data, or need more flexibility, the VBA solution is highly adaptable. Always save your work before running macros, and verify your macro security settings allow you to run the code.
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