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.

**Table of Contents**hide

**Download the Practice Workbook**

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

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

**Read more: ****COUNTIF Multiple Ranges Same Criteria in Excel**

**Similar Readings**

**Excel COUNTIFS Not Working (7 Causes with Solutions)****COUNTIF vs COUNTIFS in Excel (4 Examples)****COUNTIF Greater Than and Less Than [with Free Template]****COUNTIF between Two Cell Values in Excel (5 Examples)**

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

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

**Notes**

- The
**SUBTOTAL**function uses aas an argument that controls the behavior of the function. Depending on the*function_num***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.