How to count in Excel after filter

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.

1. Excel COUNTIF function for counting filtered cells with text
2. 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

=SUBTOTAL(103,A1:A22)

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 Practice workbook

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.

Steps

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.

Similar Reads

How to filter in Excel greater than

How to undo sort in Excel after save