So you have filtered your data, and now you need to count the excel cells. Counting Excel data that is not filtered is not hard but becomes a little bit tricky after filtering your data. In this article, I will take you through some of the methods you can use to count in Excel after filter. Therefore, check out the following procedures.
How to count filtered data in Excel
You can count filter data in Excel using the following methods.
- Excel COUNTIF function for counting filtered cells with text
- Combine SUMPRODUCT, SUBTOTAL, INDIRECT, ROW, and ISTEXT functions to count filter data in Excel
I will take you through those two methods and you can pick the one that you’ll like. Therefore, read below to know more about those methods
Excel COUNTIF function for counting filtered cells with text
Normally, Excel has a number of functions, and we apply them for different purposes. One of them is COUNTIF function, and we can use it to count filtered data. Though the method is not straightforward, and you’ll have to include a “helper column” in your table. In that column, we will make a formula alongside the “Subtotal” function. The “Subtotal” function can do a number of tasks, It depends on the function number in that AGUEMENT. Then read our steps below to understand how it works.
Use COUNTIF function to count filtered cells
Here are the steps below.
1. You can first choose an empty cell, then type this formula
Note: 103 is the value of “COUNTA” function. It becomes 1 for the valued cell or 0 if empty. A1:A22 is the range
The difference between COUNT and COUNTA is that :
- COUNT, which bears the 102 code, only counts cells with numbers
- COUNTA, on the other hand, counts cells that are not empty
Download the Practice Sheet
Count in Excel after filter using VBA code
You can use the VBA code to count only filtered rows in Excel. VBA refers to visual basics application that allows advanced Excel users to create small applications within Excel. Thus you can use VBA to count in Excel after filter. Follow these simple steps.
1. Use ALT+F11 to open the VBA editor
2. Next, click on insert, then click Module
3. Copy and paste the following code
Sub CountVisibleCells() Dim rng As Range Dim countVisible As Long Set rng = ActiveSheet.Range("A1:A27") ' Change range as per your requirement countVisible = Application.WorksheetFunction.Subtotal(3, rng) MsgBox "Number of visible cells after filter: " & countVisible End Sub
The above VBA code will count the number of cells visible after filtering. Remember to change the range according to the data on your spreadsheet.
4. Click on File and select Close and Return to Microsoft Excel
5. Go to the developer tab and click on Macros
6. Run the CountVisibleCells Macro
7. The macro will count all the visible cells after filter and display the results in a pop up window.
How to count the filtered out cells in Excel using VBA
If you want to find out the cells that have been filtered out, then you can use VBA to solve the problem.
This VBA code will count only the number of the filtered out cells.
1. After filtering in Excel, click on the Developers Tab
2. Next click on Visual Basic
3. Now click on insert then Module
4. Copy and paste the following VBA code. Remember to change the range in the VBA code to match your data
Sub CountFilteredOutCells() Dim rng As Range Dim visibleCells As Range Dim numFilteredOut As Long ' Set the range to count filtered out cells Set rng = Range("A1:A10") ' Get only the visible cells in the range Set visibleCells = rng.SpecialCells(xlCellTypeVisible) ' Loop through each cell in the original range and count the ones that are not visible For Each cell In rng If cell.EntireRow.Hidden Or cell.EntireColumn.Hidden Or Intersect(cell, visibleCells) Is Nothing Then numFilteredOut = numFilteredOut + 1 End If Next cell ' Display the result MsgBox "Number of filtered out cells: " & numFilteredOut End Sub
The VBA code above will count all the filtered out cells In Excel
How to Filter in Excel
Filtering in Excel is very easy. All you need to do is follow these simple steps.
1. Create your data using Excel. You can still use existing data
2. Highlight the range of data that you wish to filter
3. Click on data then click on the filter icon under the sort and filter section
4. Triangular icons facing downwards will appear on the first row of your data. Click on one and select number filters
5. Under this section you can now choose the filter that you wish to apply. For example I will choose the less or equal filter. I want to filter the cells that are less or equal to 300. Therefore I will fill 300 in the pop up window labeled as Custom AutoFilter
Note that you can combine two filter by utilizing the second row under the custom AutoFilter
6. Press okay.
As you can see from the image above all the data that is less or equal than 300 is visible after applying the filter. All the cells with data more than 300 has been hidden.