# 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:

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

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

`=SUBTOTAL(3,B2:B194)`

- 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. - In this example, I’ve filtered my sales dataset to show only
**January**,**April**, and**July**from the years**2022**and**2023**.

- When I hit the
`Enter`key, I get the exact count of cells of sales data for the filtered months, which is**61**. - 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:

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

- Type the
`equals`sign and then type**AGGREGATE**. - Give an opening parenthesis and choose a function from
**1**to**19**. In this example, I choose**SUM**which is**number 9**. - 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.

- 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. - The final formula is as below:

`=AGGREGATE(9,5,D2:D194)`

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

- 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:

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

- Copy and paste this formula into that cell:

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

- 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.
- In the above example, I filtered the
**Qty**column**(C2:C245)**for**Carrot**under the**Product**column**(D2:D245)**. - The
**ISFILTERED**syntax in the above formula tells**COUNTA**to only count filtered cells and not all cells under the**Qty**column. - Modify the formula according to your own Excel worksheet data and hit the
`Enter`key.

- You should get an accurate count of all the filtered cells under
**Qty**for**Carrot**in**Product**. - 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:

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

- Then, hit the
**Insert**menu and click on**Module**. - 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
```

- 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.

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

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

- 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.