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

**Table of Contents**hide

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

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

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