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

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.

Download the Practice Workbook

Download this practice workbook to exercise while you are reading this article.

SUBTOTAL COUNTIF.xlsx

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.

Similar Readings

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.

Notes

The SUBTOTAL function uses a function_num as an argument that controls the behavior of the function. Depending on the function_num value the SUBTOTAL function can calculate AVERAGE, SUM, MAX, MIN, COUNT, etc. of a range of values. Here we used 103 which ignore the hidden rows while applying the COUNTA

For example, here we hide row 8 that changed the number of products from the vegetable category from 3 to 2. And we also hide rows 13 and 14 which contain products of the meat category that resulted into 0.

Conclusion

Now, we know how to use the COUNTIF function with the SUBTOTAL function in Excel. Hopefully, it would encourage you to use this functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.