How to Count Only Filtered Cells in Excel [6 Ways]

Are you tired of counting filtered cells in Excel using conventional formulas, only to realize that the count includes hidden or filtered cells that skew your data? Relax, no need to worry! In this article, I’ll show you how to count only the filtered cells in Excel, saving you time.

With several methods to choose from in this article, surely you can find the perfect solution that meets your needs. Whether you’re a seasoned Excel user or just starting out, this article has got you covered. So let’s dive in and explore several ways to count only filtered or visible cells in Excel.

Importance of Knowing to Count Only Filtered Cells in Excel

Learning how to count only filtered cells in Excel is important because it’s a systematic way to improve the accuracy of your data analysis. When working with large datasets, it’s common to use filters to sort and analyze specific subsets of data.

However, when you use the regular COUNT or COUNTA functions, they include all cells in the selected range, including those that are hidden or filtered. This always leads to incorrect calculations and analysis.

When you know how to count only filtered cells, you can accurately calculate the number of visible cells, ensuring that your analysis is based on the correct data. This skill is particularly useful when working with complex data sets that require advanced filtering and analysis techniques. Plus this Excel skill saves you time as you don’t need to manually count the filtered cells.

1. Count Only Filtered Cells in Excel Using SUBTOTAL

The most common Excel function to count only filtered cells in an Excel database or table is the SUBTOTAL function. You’ll find this formula in Excel versions as early as Excel 2007 to the latest Excel for Microsoft 365 desktop apps.

The SUBTOTAL function is pretty simple as it only has two arguments. The first one is an integer that tells the function which mathematical operator to use like SUM, COUNT, etc. Then, you just need to specify the cell ranges after a comma.

The first argument of the SUBTOTAL function is of two types. From 1 to 11, the formula counts all hidden rows in a filtered column. However, when you use 101 to 111, it doesn’t consider any hidden rows. Let’s find out below how it works:

1. Open your Excel worksheet where you’ve got a data table that you can filter.
2. Now, select the cell where you want to fetch total filtered cell counts or the summation of filtered cell values only.

3. Then, copy and paste the following formula into that cell:

=SUBTOTAL(3,B2:B194)

4. In the above formula, number 3 is for the COUNTA function and B2:B194 is the cell reference within which SUBTOTAL will apply the COUNTA function.
5. In this example, I’ve filtered my sales dataset to show only January, April, and July from the years 2022 and 2023.

6. When I hit the Enter key, I get the exact count of cells of sales data for the filtered months, which is 61.
7. Don’t forget to modify the cell ranges depending on your own Excel worksheet.

You can also use SUBTOTAL to perform other functions on the selected cell ranges within a filtered data column. For example, you can choose AVERAGE (1), MAX (4), SUM (9), etc. If you suspect your data table could contain hidden cells in the column where you’re performing SUBTOTAL, choose arguments like AVERAGE (101), MAX (104), SUM (109), etc.

2. Count Filtered or Visible Cells Using AGGREGATE

AGGREGATE and SUBTOTAL are similar functions. Hence, you can also use AGGREGATE to count only filtered or visible cells in Excel. Its advantages over the SUBTOTAL function are as outlined below:

• You can choose from 19 different functions and mathematical operators inside the AGGREGATE formula.
• Available in a wide range of Excel web apps and desktop apps.
• You can set calculation criteria like the following:
  • Ignore hidden rows
  • Ignore error values
  • Ignore nested SUBTOTAL and AVERAGE functions to avoid skewed results
• By default, AGGREGATE ignore filtered data in a column or table.

Let’s find below how to apply AGGREGATE in a cell to count filtered cells or apply other calculations only to filtered cell values below:

1. Create or import a table of data that you can filter.
2. Select the cell where you must apply the AGGREGATE function.

3. Type the equals sign and then type AGGREGATE.
4. Give an opening parenthesis and choose a function from 1 to 19. In this example, I choose SUM which is number 9.
5. Type in a comma and choose the integer for Options from 1 to 7. For instance, I selected 5 to ignore filtered-out cells or the hidden ones.

6. Now, after a comma, simply type in or select the cell range that you want to include in the calculation. In this tutorial, the cell range is D2:D194, that is the Quantity column after applying a filter in the Date column.
7. The final formula is as below:

=AGGREGATE(9,5,D2:D194)

8. Hit the Enter button and you get an accurate SUM of all the filtered cells in less than a second, which is 3547.

9. To use any other function like you want to know how many cells are there after the filter, select number 3 for COUNTA. The formula will be as below:

=AGGREGATE(3,5,D2:D195)

3. Utilize the FILTER Function to Count Filtered Cells Only

Find below how you can use the FILTER function along with COUNTA to get the total number of filtered cells in a column:

1. Select the cell where you want to get a count of the filtered cells.

2. Copy and paste this formula into that cell:

=COUNTA(FILTER(D2:D245,C2:C245="carrot",ISFILTERED(C2:C245)))

3. When you use the above formula, you don’t need to filter the data table on your Excel worksheet. The formula itself includes the filter formula.
4. In the above example, I filtered the Qty column (C2:C245) for Carrot under the Product column (D2:D245).
5. The ISFILTERED syntax in the above formula tells COUNTA to only count filtered cells and not all cells under the Qty column.
6. Modify the formula according to your own Excel worksheet data and hit the Enter key.

7. You should get an accurate count of all the filtered cells under Qty for Carrot in Product.
8. If you wish to sum all the values in filtered cells, use the SUM function in place of COUNTA in the same formula. Also, you can use the AVERAGE function to get a mean of the filtered cells.

4. Use the Excel Status Bar to Count or Sum Filtered Cells

If your Excel worksheet data table isn’t large enough and you only need to get a glimpse into quick filtered column data like Average, Count, and Sum, you can use the Excel Status Bar. Apply the filter as you would normally.

Now, click the first cell just below the filtered column. Now, press and hold the Ctrl, Shift, and Down Arrow keys on your keyboard to select all cells in that column. Now, look at the bottom right corner of the worksheet near the Normal view icon of Excel.

You should see the Average, Count, and Sum of the selected cells. The Excel Status Bar shows the Count, Average, and SUM of visible cells only so it also works if you don’t have time to apply the formulas I discussed so far.

5. Use a PivotTable to Count Filtered Cells in Excel

If your data is suitable for PivotTable, you can create it and use the COUNT function to count only the filtered cells. Just add the column you want to count as a Value in the PivotTable, and then apply the filter of your choice. The PivotTable will update to show only the count of visible cells.

6. Utilize This VBA Code to Count Only Filtered Cells in Excel

If you need to automate the filtered cell counting process in an Excel worksheet, you can use a VBA code. Here’s how:

1. Open the Excel workbook that contains your database.
2. Apply the filter of your choice for a column of data.
3. Now, create a new VBA macro in the worksheet by opening the VBA Editor.
4. To open this tool, hit Alt + F11 keys together.

5. Then, hit the Insert menu and click on Module.
6. Copy and paste the following VBA script into the Module:

Sub CountFilteredCells()
    Dim myRange As Range
    Dim countVisible As Long
    
    'Change the range to the one you want to count
    Set myRange = Range("D2:D245")
    
    'Apply filter to the range
    myRange.AutoFilter Field:=1
    
    'Count the visible cells in the range
    countVisible = myRange.SpecialCells(xlCellTypeVisible).Count
    
    'Display the count result in a message box
    MsgBox "The number of filtered cells in the range is: " & countVisible
    
    'Remove the filter from the range
    myRange.AutoFilter
    
End Sub

7. In the above script, replace the cell reference in the code element Range(“D2:D245”) with the real cell range that you want to count. For example, in the above example, I’m counting these cells D2:D245 under the Qty column.

8. Click Save and follow the onscreen prompts to save the workbook as an Excel Macro-Enabled Workbook from the Save As dialog box.

9. Now, hit Alt+ F8 to bring up the Macro dialog box.
10. Select the CountFilteredCells macro and hit the Run button.

11. A message box will show the exact count of the cells in the filtered column.

Conclusions

So, these are pretty much all of the proven and common methods to count only filtered cells in Excel. For a quick peak into the various (Sum, Average, and Count) parameters of a filtered data column, you can check the Excel Status Bar after selecting the entire column.

If you’re a mid-level Excel user and okay with custom formulas other than what you get on the Excel ribbon menu, go for any of the functions like SUBTOTAL, AGGREGATE, and FILTER to infer the count of visible and filtered cells. You can also try the PivotTable method if the data table is suitable enough.

When you need advanced automation in your Excel worksheet and you want to use a VBA script, use the VBA macro mentioned in this article to only count filtered and visible cells. If you know a better method and want to share your experience with the above Excel tips, don’t forget to use the comment section.