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.


Count Filtered Rows with Criteria in Excel: 5 Easy Ways

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 wanted to filter the other values.
  • Click OK.

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


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, returns 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, insert 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, the OFFSET function returns 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)


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 to any number of rows.
  • Initially, it shows 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 Visible Rows in Excel


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

The AGGREGATE function is also very effective in counting 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 row number has automatically changed.


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.


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


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.

Download Practice Workbook

Download this sample file to try it by yourself.


Conclusion

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


<< Go Back to Count Rows | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo