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.

## How to Aggregate COUNTIF Function in Excel: 4 Ways

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 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 theargument that refers to the*range**Sales*. In contrast, the**”>1500”**cell is theargument that returns the count of the matched values.*criteria***Output → 6**

**COUNTIF(D5:D14,”>=4000”) →**In this formula, the**D5:D14**cells represent theargument that refers to the*range**Sales*. In contrast, the**”>=4000”**cell is theargument that returns the count of the matched values.*criteria***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

__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 theargument that refers to the*range**Sales*. In contrast, the**D16**cell is theargument that returns the count of the matched values.*criteria***Output → 2**

**COUNTIF(B5:B14,D17) →**In this expression, the**D5:D14**cells represent theargument that refers to the*range**Sales*. In contrast, the**D17**cell is theargument that returns the count of the matched values.*criteria***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

__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 theargument that refers to the*range**Dates*. In contrast, the**{“01-9-21″,”02-25-21”}**string of text is theargument that returns the count of the matched values.*criteria***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 theargument which is added together to return the number of occurrences of the specified criteria.*array1***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.

## 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 theargument that refers to the*range**Sales*. In contrast, the**C17**cell is theargument that returns the count of the matched values.*criteria***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.

**Download Practice Workbook**

You can download the practice workbook from the link below.

## 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.

## Related Articles

- AGGREGATE vs SUBTOTAL in Excel
- AGGREGATE Formula for Adding Serial Number in Excel
- How to Use AGGREGATE to Achieve MAX IF Behavior in Excel
- Combining AGGREGATE with IF Function in Excel
- How to Combine INDEX and AGGREGATE Functions in Excel

**<< Go Back to ****Excel AGGREGATE Function** **|** **Excel Functions** **|** **Learn Excel**