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
COUNTIFfunction. 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
SUMPRODUCTfunction. 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:
You can make your own copy of the spreadsheet above using the link attached below.
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.
