# How to Use SUBTOTAL with COUNTIF in Excel

This guide will discuss how to use `SUBTOTAL` with `COUNTIF` in Excel.

The rules for using the `COUNTIF` function in Excel are the following:

• When the selected strings to match are longer than 255 characters, the function will return incorrect results.
• We always have to enclose the criteria argument in quotations.
• When we input a cell reference that is linked to another workbook, the function will return a #VALUE! error.
• We can use wildcard characters such as question marks and asterisks for the criteria argument.

Excel is an excellent tool for calculating data values in a data set. Since it contains several built-in functions and tools, we can easily perform different calculations in different situations.

In this guide, we will focus on how to use the `SUBTOTAL` function with the `COUNTIF` function to calculate the count of a filtered data set. When we filter out a large data set to only display certain data values, the function will still count the hidden values and include them in the calculations.

However, sometimes we only want to calculate the visible rows or data values of the data set. For instance, we want to count the number of cells that meet our given criteria based on the filtered data set.

And sometimes, the function will count the entire data set. To prevent this, we need to utilize the `SUBTOTAL` function, the `COUNTIF` function, and the `SUMPRODUCT` function.

Let’s take a sample scenario wherein we need to use `SUBTOTAL` with `COUNTIF` in Excel.

Suppose you have a data set containing a list of different class scores and gender of the students. And you want to count the number of girls in a specific class. Instead of creating a new data set, you opted to simply filter the specific class.

However, the function is returning the incorrect result. So you opted to combine the `SUBTOTAL` and `COUNTIF` functions to obtain the correct count.

Before we move on, let’s first discuss how to write the `COUNTIF` function in Excel.

## The Anatomy of the COUNTIF Function

The syntax or the way we write the `COUNTIF` function is as follows:

`=COUNTIF(range, criteria)`

Let’s take apart this formula and understand what each term means:

• = the equal sign is how we start any function in Excel.
• COUNTIF() refers to our `COUNTIF` function. And this function is used to count the number of cells within a selected range that meet the given criteria or condition.
• range is a required argument. And this refers to the specific range containing the cells we want to count.
• criteria is another required argument. So it refers to the condition in the form of a number, expression, or text that will define what specific cells will be counted.

## The Anatomy of the SUMPRODUCT Function

The syntax or the way we write the `SUMPRODUCT` function is as follows:

`=SUMPRODUCT(array1, array2)`

Let’s take apart this formula and understand what each term means:

• = the equal sign is how to begin any function in Excel.
• SUMPRODUCT() is our `SUMPRODUCT` function. And this function is used to return the sum of the products of corresponding ranges or arrays we input.
• array1 is a required argument. So this refers to 2 to 255 arrays for which we want to multiply. Then, the function will add the components. Additionally, all the arrays must have the same dimensions.
• array2 is an optional argument. And this is also referring to 2 to 255 arrays which we will multiply and add the components.

Great! Now let’s dive into a real example of using `SUBTOTAL` with `COUNTIF` in Excel.

## A Real Example of Using SUBTOTAL with COUNTIF in Excel

Let’s say we have a data set containing three columns. So the first column is the different classes. And the second is the gender of the student. Lastly, the third column contains the scores of the student. So our initial data set would look like this:

We want to count the number of girls in a specific class in the data set. Rather than creating a new data set, we will filter the specific class we want to count. Then, we will proceed to count using the filtered data set.

However, we cannot perform this task using the `COUNTIF` function alone because it will count the entire cells from the original data set.

So the `SUBTOTAL` function is used to return the subtotal in a list or database. Then, the `COUNTIF` function counts the number of cells that meet the given condition or criteria.

But, sometimes, the `SUBTOTAL` function cannot handle the inputted criteria of the `COUNTIF` function. So we need to pair the two functions with the `SUMPRODUCT` function, which will filter the count number.

Firstly, we will filter our original data set. Since we only want to count specific cells from one class, we want only to display data values from that class.

Secondly, we can now use the combined formula to count the number of cells that meet our given criteria based on the filtered data set.

So our final data set would look like this:

Amazing! We can now begin to discuss the steps of how to use `SUBTOTAL` with `COUNTIF` in Excel.

## How to Use SUBTOTAL with COUNTIF in Excel

In this section, we will explain the step-by-step process of how to use `SUBTOTAL` with `COUNTIF` in Excel. To apply this method to your work, you can simply follow the steps below.

1. Firstly, we need to filter our initial data set. To do this, we will select the entire range and go to the Data tab. Then, we will select Filter within the Sort & Filter section.

2. Secondly, we will click the dropdown arrow beside the Class column. Next, we will check the box for B since we only want to count the cells from class B. Lastly, we will press Apply.

3. After we have filtered the data set, we can now apply the formula. To do this, we can simply input the formula “=SUMPRODUCT(SUBTOTAL(3, OFFSET(C4:C9, ROW(C4:C9)-ROW(C4),0,1)),–(C4:C9=”girl”))”. Then, we will press the Enter key to return the result.

4. And tada! We have successfully used `SUBTOTAL` with `COUNTIF` in Excel.

And that’s pretty much it! We have explained how to use `SUBTOTAL` with `COUNTIF` in Excel. Now you can apply this to your work when you need to count specific cells in your filtered data set.

Are you interested in learning more about what Excel can do? You can now use the `COUNTIF` function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

## How to Use IF Statement Between Two Numbers or Dates

This guide will explain how to use the IF statement between two numbers or dates using the IF…

## How to Use Solver in Excel

This guide will help you understand how to use the Solver add-in program for what-if analysis in Microsoft…

## How to Quickly Generate Random Passwords in Excel

This guide will explain how you can use Excel functions to quickly generate random passwords in Excel. The…

## How to Calculate Stock to Sales Ratio in Excel

This guide will explain how to calculate the stock to sales ratio in Excel. When we want to…