Eshrak Kader
Microsoft Excel is the go-to software for many businesses looking to store company data and oftentimes, we may need to aggregate COUNTIF function to solve the problem having multiple criteria. In this tutorial, we’ll show 4 easy ways how to aggregate COUNTIF in Excel.
Download Practice Workbook
You can download the practice workbook from the link below.
4 Ways to Aggregate COUNTIF Function in Excel
Now, let us consider the Sales Data dataset in the B4:D14 cells which contain the Product names, the sales Date, and the Sales in USD. Here, we want to count the number of occurrences of the given criteria. Therefore, without further delay let’s look at each method in detail.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.
Method-1: Utilizing COUNTIF Function with Greater Than and Less Than Criteria
Let us begin by applying greater than and less than criteria to the COUNTIF function to obtain the values fulfilling this condition. Basically, we want to know the Sales values between $1500 and $4000, therefore, just follow the steps shown below.
📌 Steps:
- Initially, move to the C19 cell and insert the following expression in the Formula Bar.
=COUNTIF(D5:D14,">1500")-COUNTIF(D5:D14,">=4000")
Here, the D5:D14 range of cells refers to the Sales values.
Formula Breakdown:
- COUNTIF(D5:D14,”>1500”) → counts the number of cells within a range that meet the given condition. Here, the D5:D14 cells point to the range argument that refers to the Sales. In contrast, the ”>1500” cell is the criteria argument that returns the count of the matched values.
- Output → 6
- COUNTIF(D5:D14,”>=4000”) → In this formula, the D5:D14 cells represent the range argument that refers to the Sales. In contrast, the ”>=4000” cell is the criteria argument that returns the count of the matched values.
- Output → 4
- COUNTIF(D5:D14,”>1500″)-COUNTIF(D5:D14,”>=4000″) → becomes
- 6-4 → 2
Finally, your output should look like the picture given below.
Read More: How to Aggregate Data in Excel (3 Easy Ways)
Method-2: Aggregate 2 Criteria with COUNTIF Function
For our second method, we’ll aggregate two criteria with the COUNTIF function to get the count of the matching values. In this case, we want to count the instances of the Product AC and TV in the dataset, so, let us see how we can achieve this in the steps below.
📌 Steps:
- In the first place, navigate to the C19 and type in the following expression.
=COUNTIF(B5:B14,D16)+COUNTIF(B5:B14,D17)
Here, the B5:B14 range represents all the Products while the D16 and D17 refer to the Criteria AC and TV.
Formula Breakdown:
- COUNTIF(B5:B14,D16) → counts the number of cells within a range that meet the given condition. Here, the B5:B14 cells point to the range argument that refers to the Sales. In contrast, the D16 cell is the criteria argument that returns the count of the matched values.
- Output → 2
- COUNTIF(B5:B14,D17) → In this expression, the D5:D14 cells represent the range argument that refers to the Sales. In contrast, the D17 cell is the criteria argument that returns the count of the matched values.
- Output → 2
- COUNTIF(B5:B14,D16)+COUNTIF(B5:B14,D17) → becomes
- 2+2 → 4
Eventually, the result should look like the image shown below.
Read More: How to Use Excel AGGREGATE Function with Multiple Criteria
Method-3: Aggregate 3 Criteria with COUNTIF Function
Similar to the previous method, we can add three criteria to the COUNTIF function to determine the occurrences of the Product AC, TV, and Oven within the given dataset, so let’s see it in action.
📌 Steps:
- To begin with, jump to the C19 cell and insert the expression in the Formula Bar.
=COUNTIF(B5:B14,D16)+COUNTIF(B5:B14,D17)+COUNTIF(B5:B14,D18)
Here, the D16, D17, and D18 cells point to the Criteria AC, TV, and Oven respectively.
Lastly, the results should look like the screenshot given below.
Read More: How to Use Conditional AGGREGATE Function in Excel (2 Methods)
Method-4: Aggregate with COUNTIF and SUMPRODUCT Functions
Alternatively, we can combine the COUNTIF and SUMPRODUCT functions to check if certain dates are present in the dataset. Now, allow me to demonstrate the process in the steps below.
📌 Steps:
- First, proceed to the C19 cell and insert the following equation.
=SUMPRODUCT(COUNTIF(C5:C14,{"01-9-21","02-25-21"}))
Here, the C5:C14 cells represent the Dates.
Formula Breakdown:
- COUNTIF(C5:C14,{“01-9-21″,”02-25-21”}) → Here, the C5:C14 cells point to the range argument that refers to the Dates. In contrast, the {“01-9-21″,”02-25-21”} string of text is the criteria argument that returns the count of the matched values.
- Output → {2, 2}
- SUMPRODUCT(COUNTIF(C5:C14,{“01-9-21″,”02-25-21”})) → becomes
- SUMPRODUCT({2, 2}) → returns the sum of the products of the corresponding ranges or arrays. Here, the {2, 2} is the array1 argument which is added together to return the number of occurrences of the specified criteria.
- Output → 4
📃 Note: Make sure to press CTRL+SHIFT+ENTER instead of pressing ENTER while using any other versions of Excel except for Microsoft Excel 365.
Consequently, this should yield the result shown in the image below.
Read More: How to Use AGGREGATE Function in Excel (13 Examples)
Using COUNTIF Function to Aggregate the Counting Values in Excel
Lastly, a simple but important application of the COUNTIF function returns the count of the Sales that are greater than $1500. Hence, let’s see the process in detail.
📌 Steps:
- First of all, go to the C19 cell >> enter the formula given below.
=COUNTIF(D5:D14,C17)
Here, the C17 cell refers to the Criteria of Sales greater than $1500 while the D5:D14 range of cells indicates the Sales values.
Formula Breakdown:
- COUNTIF(D5:D14,C17) → counts the number of cells within a range that meet the given condition. Here, the D5:D14 cells represent the range argument that refers to the Sales. In contrast, the C17 cell is the criteria argument that returns the count of the matched values.
- Output → 6
Finally, the results should look like the image shown below.
Practice Section
We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.
Conclusion
I hope this article helps you understand how to use COUNTIF to aggregate in Excel. If you have any queries, please leave a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.
