# How to Use COUNTIF with SUBTOTAL in Excel (2 Methods)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we’ll illustrate how to use the COUNTIF function with the SUBTOTAL function in Excel to count filtered data using two convenience methods.

## 2 Methods of Using COUNTIF with SUBTOTAL in Excel

In this article, we used the following dataset to demonstrate 2 different methods to find the number of visible rows only. Here is a list of products of 4 different categories. We’ll find out the number of products under each category. Let’s follow the guide. ### 1. Use of COUNTIF with SUBTOTAL to Count Filtered Data With Criteria Using Excel Functions

Usually, the SUBTOTAL function cannot handle criteria that are put by the COUNTIF function. So we can filter the count number using the SUMPRODUCT function with both the SUBTOTAL function (via the OFFSET function) and the criteria.

In cell E6 put the following formula:

`=SUMPRODUCT((C5:C14=C5)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C14)-MIN(ROW(C5:C14)),0))))` Formula Breakdown:

The SUMPRODUCT function takes arrays as its input. In this formula, we put the first input array as the criteria and the second input array handles the visibility. The criteria are-

`=(C5:C14=C5)` This checks the value of C5 that is Fruit against the range C5:C14. It outputs an array shown in the screenshot below. As this array is in the multiplication form in the formula it eventually converts into an array of 1’s and 0’s.

{1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0} Now, in the second part of the formula, we have the SUBTOTAL function which returns a single value as an output. But we need to put an array input in the SUMPRODUCT function. So, we need to use the OFFSET function as an input of the SUBTOTAL function, one reference per row which returns one result per row. This requires putting an array that contains one number per row starting with zero as the input of the OFFSET function. Put this formula below to acquire this array:

`=ROW(C5:C15)-MIN(ROW(C5:C14))` Let’s put above formula into the OFFSET function, that is:

`=OFFSET(C5,ROW(C5:C14)-MIN(ROW(C5:C14)),0)` Finally, the SUBTOTAL function returns an array of 1’s and 0’s.

`=(SUBTOTAL(103,OFFSET(C5,ROW(C5:C14)-MIN(ROW(C5:C14)),0)))` Now we can understand how the 2nd part of the formula works, let’s move to the final step.

`=SUMPRODUCT( criteria * visibility )`

Put the formula into any blank cell-

`=SUMPRODUCT(D5:D14*H5:H14)` The range D5:D14 represents the criteria and the range H5:H14 represents visibility. The result is 3 which is the number of Fruit products in the products list.

Similarly, we can filter and get the number of products for each category. ### 2. Excel COUNTIFS Function to Count Filter Data with Criteria by Adding a Helper Column

In this method, first, we’ll add a helper column and then use the SUMIFS function to count the number of products based on their categories. Follow the steps below:

Steps:

• In cell D4, write the following formula
`=IF(C4="Fruit",1,0)`

This formula checks if the value of cell C4 is Fruit or not. If the value is fruit then it will show 1 or 0 otherwise. • Using the fill handle, copy and paste the formula through the column. • The result shows clearly that the cells with Fruit Category show 1 and the cells with categories other than Fruit show 0 as output. • Now put the following formula in a blank cell (in this example cell I7 )you want to store the result.
`=COUNTIFS(C4:C13,"Fruit",D4:D13,"1")` In this formula, the COUNTIFS function checks two criteria in two ranges and returns the number of matches. In the range C4:C13 it matches for Fruit and in the range D4:D13 it matches for 1.

• By following the above steps we can calculate the number of products each category has very easily.  #### Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  