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

In this article, we’ll demonstrate how to count filtered data using the COUNTIF function together with the SUBTOTAL function.

Download the Practice Workbook

2 Methods for Using COUNTIF with SUBTOTAL in Excel

We’ll use the following dataset, which contains a list of products in 4 different categories, to find the number of visible rows only. We’ll find out the number of products under each category.

excel subtotal countif

Method 1 – Counting Filtered Data With Criteria Using Excel Functions

Usually, the SUBTOTAL function cannot handle criteria that are given by the COUNTIF function. But 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 enter the following formula:

excel subtotal countif

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.

excel subtotal countif

The criteria are:


This checks the value of C5 (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 resolves into an array of 1’s and 0’s.

{1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0}

In the second part of the formula, the SUBTOTAL function returns a single value as an output. But we need to put an array input in the SUMPRODUCT function, so we 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.

  • Enter the formula below to acquire this array:

excel subtotal countif

  • Put the above formula into the OFFSET function:

excel subtotal countif  

The SUBTOTAL function returns an array of 1’s and 0’s.


excel subtotal countif

Now we can apply the formula:

=SUMPRODUCT( criteria * visibility )
  • Enter the following formula into any blank cell:

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 return the number of products for each category.

excel subtotal countif

Read More: COUNTIF Multiple Ranges Same Criteria in Excel

Similar Reading

Method 2 – Using COUNTIFS Function to Count Filter Data with Criteria by Adding a Helper Column

First we’ll add a helper column and then use the SUMIFS function to count the number of products based on their categories.


  • In cell D4, enter the following formula:

This formula checks if the value of cell C4 is Fruit or not. If the value is Fruit then it will show 1 else 0.

excel subtotal countif using column handler

  • Using the Fill Handle, copy the formula to the rest of the column.

excel subtotal countif

The result is that the cells with Fruit category show 1 and the cells with categories other than Fruit show 0 as output.

  • Enter the following formula in a blank cell (in this example cell I7) where you want to store the result.

excel subtotal countif

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.

  • The number of products in each category is returned.

Read More: How to Use Excel COUNTIF That Does Not Contain Multiple Criteria


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 ignores the hidden rows while applying the COUNTA function.

For example, here we hide row 8, which reduces the number of products from the Vegetable category from 3 to 2. We also hide rows 13 and 14 which contain products of the Meat category, reducing the count to 0.

excel subtotal countif

Further Reading

Get FREE Advanced Excel Exercises with Solutions!
Al Arafat Siddique
Al Arafat Siddique

Al Arafat Siddique, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has worked on the ExcelDemy project for two years. He has written over 85+ articles for ExcelDemy. Currently, he is working as a software developer. He is leading a team of six members to develop Microsoft Office Add-ins, extending Office applications to interact with office documents. Other assigned projects to his team include creating AI-based products and online conversion tools using the latest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF