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

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel Count Filtered Rows with Criteria

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.

Insert SUMPRODUCT Function to Count Filtered Rows with Criteria in Excel

  • Now, insert this formula in cell C17.

=SUMPRODUCT((B5:B14=C16)*(SUBTOTAL(103,OFFSET(B5,ROW(B5:B14)-MIN(ROW(B5:B14)),0))))

Insert SUMPRODUCT Function to Count Filtered Rows with Criteria in Excel

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

Here, we applied

  • The SUMPRODUCT function in the beginning to return the sum product of the selected cell range B5:B14, based on the criteria in cell C17.
  • Then, used the SUBTOTAL function to get a return of the cumulative total with the function_num as 103. It defines that it will only count the visible cells in the dataset.
  • Following, inserted the ROW function to return the row number from cell range B5:B14.
  • The MIN function is used to get the lowest value from those selected cells.
  • Lastly, applied the OFFSET function to return the specified criteria in cell C16 from these rows and columns with 0 for an exact match.

Note: You can also insert this formula to count filtered rows with criteria. =SUMPRODUCT(SUBTOTAL(3,OFFSET(B5:B14,ROW(B5:B14)-MIN(ROW(B5:B14)),,1)),ISNUMBER(SEARCH("Orange",B5:B14))+0)

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.

=SUBTOTAL(3,B5:B14)

Count Filtered Rows with Criteria Using SUBTOTAL Function

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

Excel Count Filtered Rows with Criteria

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


Similar Readings


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.

Apply AGGREGATE Function for Counting Filtered Rows with Criteria in Excel

  • Now, insert this formula in cell C16.

=AGGREGATE(3,3,B5:B14)

Apply AGGREGATE Function for Counting Filtered Rows with Criteria in Excel

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

=IF(B5="Orange",1,"")

Count Filtered Rows with Criteria Inserting Help Column

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

=COUNTIFS(B5:B14,"Orange",E5:E14,"1")

Count Filtered Rows with Criteria Inserting Help Column

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

Excel VBA to Count Filtered Rows with Criteria

  • 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

Excel VBA to Count Filtered Rows with Criteria

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

=COUNTVISIBLE(B5:B13)

  • 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

Sanjida Mehrun Guria
Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo