How to Count Filtered Rows with Criteria in Excel (5 Easy Ways)

In Excel, the Filter is a very powerful tool to fetch specific data from a large dataset. Along with filtering, we sometimes need to count those filtered rows with criteria. There are many options to do so in excel. In this article, we will guide you on how to count filtered rows with criteria in excel in 5 easy ways.

Download Practice Workbook

Download this sample file to try it by yourself.

5 Easy Ways to Count Filtered Rows with Criteria in Excel

For illustration, here is a dataset with the information on the Sales Report in July. This dataset shows the number of fruit sales in the USA and Australia.

Now, let’s filter this dataset first. Simply follow the steps below:

• In the beginning, select cell range B4:D14.
• Next, go to the Home tab and select Filter from the Sort & Filter drop-down in the Editing group.

• Otherwise, go to the Data tab and you will also find the Filter tool in the Sort & Filter group.

• Now, you will see the filter is enabled in the dataset.

• To filter this, click on the arrow beside the Product column.
• For example, we deselected Kiwi as we want to filter the values.
• Click OK.

• Finally, you will get your filtered rows without the fruit Kiwi like this:

Now, let’s count these filtered rows with criteria following the methods below.

1. Insert SUMPRODUCT Function to Count Filtered Rows with Criteria in Excel

In this first method, we will insert the SUMPRODUCT function to count the filtered rows based on specific criteria. This function will return the sum of the products from the corresponding cell range. Follow the steps below:

• First, insert your preferred criteria which you will count rows.
• For example, we gave the product Orange as the Criteria in cell C16.

• Now, insert this formula in cell C17.

• After this, press Enter.
• Finally, you will see the number of rows containing the special criteria.

Read More: Count Cells That Are Not Blank in Excel (6 Useful Methods)

2. Count Filtered Rows with Criteria Using SUBTOTAL Function

At this stage, let us find out the process to count filtered rows with criteria using the SUBTOTAL function. It will return the total number of cells for the selected range. Let’s find out the process below:

• First, insert this formula in cell C16.

• Then, press Enter.
• Here, we have created the general formula which will be applicable for any number of rows.
• Initially, it is showing the total number of rows before filtering.

Here, we used the SUBTOTAL function to return the total number of selected criteria. The function_num argument is defined as 3 to specify the COUNTA function for visible cells in the cell range B5:B14.

• Now, specify the criteria from the Filter context menu. We deselect Kiwi, Strawberry and Watermelon.

• After this, press OK.
• Finally, we successfully counted the filtered rows with criteria. The result is shown in cell C16.

Read More: How to Count If Cell Contains Number (Easiest 7 Ways)

Similar Readings

• How to Count Blank Cells in Excel (5 Ways)
• Count Empty Cells in Excel (4 Ways)
• Excel Count Cells with Numbers (5 Simple Ways)
• How to Count Cells with Specific Text in Excel (Both Case Sensitive and Insensitive)
• Count Cells that Contain Specific Text in Excel

3. Apply AGGREGATE Function for Counting Filtered Rows with Criteria in Excel

The AGGREGATE function is also very effective to count filtered rows based on specific criteria. It also works to ignore hidden cells and find out the exact values from the database. Let’s see how it works.

• First, filter the product names to Apple and Banana defining them as criteria.

• Now, insert this formula in cell C16.

• Following, press Enter.
• That’s it, you will see the number of rows for the selected criteria.

Here, the AGGREGATE function creates the opportunity to avoid hidden rows or error values from the cell range B5:B14.

• To verify the formula, let us add Orange to the criteria from the Filter list.

• Next, hit OK.
• Therefore, you can see the counted rows number has automatically changed.

Read More: How to Count Only Visible Cells in Excel (5 Tricks)

4. Count Filtered Rows with Criteria Inserting Help Column

Another easy way to count filtered rows is to insert a help column and afterward apply the IF function and the COUNTIFS function successively. Let’s check the steps below:

• Firstly, create a Help Column beside the original filtered dataset.
• Secondly, insert this formula in cell D5.

• Thirdly, press Enter.
• Fourthly, use the Fill Handle tool to drag this formula up to cell E14.

Here, we used the IF function to compare our selected criteria Orange with the value in cell B5.

• Now, insert this formula in cell C16.

• Lastly, press Enter and you will see the counted value of the cells holding criteria.

Here, we applied the COUNTIF function to count cell range B5:B14 which meets the condition in cell range E5:E14.

Read More: Excel Formula to Count Cells with Text (All Criteria Included)

5. Excel VBA to Count Filtered Rows with Criteria

This last method will guide you to apply excel VBA code for counting filtered rows with criteria. Following are the steps:

• First, filter your dataset according to your preferred criteria like this:

• Next, go to the Data tab and select Visual Basic under the Code group.

• Following, choose Module from the Insert section.

• Then, insert this code on the blank page.

Function COUNTVISIBLE(Rw)
    Dim xCnt As Long
    Dim xRng As Range
    Application.Volatile
    For Each xRng In Rw
        If (Not xRng.EntireRow.Hidden) And (Not xRng.EntireColumn.Hidden) Then
            xCnt = xCnt + 1
        End If
    Next
    COUNTVISIBLE = xCnt
End Function

• Now, save the code and close this window.
• Thereafter, insert this formula in cell C16.

• Lastly, press Enter and you will see the number of visible rows.

Here, we applied the COUNTVISIBLE function to calculate the visible cells in the dataset. It will ignore the hidden cells during counting.

Read More: How to Count Filled Cells in Excel Using VBA (7 Methods)

Things to Remember

• You can not use the COUNT function to perform this because it will select the whole dataset. Therefore, it will show a false result after filtering.
• Make sure there is no blank cell in the dataset.

Conclusion

Concluding the article with the hope that it was a beneficial article for you on how to count filtered rows with criteria in excel in 5 easy ways. Let us know your insightful suggestion. Follow ExcelDemy for more excel blogs.

Related Articles

• Count Number of Cells with Dates in Excel (6 Ways)
• How to Count Odd and Even Numbers in Excel (3 Easy Ways)
• Count Blank Cells in Excel with Condition (3 Methods)
• How to Count Cells with Specific Value in Excel (5 Easy Methods)
• How to Count Filled Cells in Excel (5 Quick Ways)
• Excel Count Number of Cells in Range (6 Easy Ways)
• How to Count Non Blank Cells with Condition in Excel (6 Methods)