How to Aggregate COUNTIF in Excel (4 Quick Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

excel aggregate countif

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

Utilizing COUNTIF Function with Greater Than and Less Than Criteria

Finally, your output should look like the picture given below.

excel aggregate countif for a range

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

Aggregate 2 Criteria with COUNTIF Function

Eventually, the result should look like the image shown below.

excel aggregate countif with 2 criteria

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.

Aggregate 3 Criteria with COUNTIF Function

Lastly, the results should look like the screenshot given below.

excel aggregate countif with 3 criteria

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.

Aggregate with COUNTIF and SUMPRODUCT Functions

Consequently, this should yield the result shown in the image below.

Result with SUMPRODUCT function

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

Using COUNTIF Function to Aggregate the Counting Values in Excel

Finally, the results should look like the image shown below.

Result with greater than condition


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.

Practice Section


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.


Related Articles

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo