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.