Certainly, the heart and soul of an Excel spreadsheet are its functions, and typically, we nest multiple functions to analyze and organize data. Keeping this in mind, we’ll explore 8 handy ways how to use nested **COUNTIF** in Excel in this article. In addition, we’ll also learn how to apply the **COUNTIF** and **COUNTIFS** functions with multiple criteria in single and double columns respectively.

First of all, let’s consider the Fruit Sales dataset shown in the **B4:D13** cells, which contains the Item Name, Quantity, and Unit Price respectively. Here, we want to combine the Excel nested **COUNTIF **function with other functions, so without further delay, let’s see each method in detail and with the appropriate illustration.

Here, we have used the *Microsoft Excel 365* version, you may use any other version according to your convenience.

## 1. Nesting Two COUNTIF Functions

First and foremost, let’s start with a simple example of an Excel nested **COUNTIF** function (the **OR **criteria). Here, the **COUNTIF** function counts the number of instances where the *Quantity* is greater than *25* or the *Unit Price* exceeds *$1.50*.

📌 ** Steps**:

- First, go to the
**D16**cell >> enter the formula given below.

`=COUNTIF(C5:C13,">25")+COUNTIF(D5:D13,">1.5")`

For instance, the **C5:C13** and **D5:D13** ranges refer to the *Quantity* and *Unit Price *columns respectively.

**Formula Breakdown:**

**COUNTIF(C5:C13,”>25″) →**counts the number of cells within a range that meet the given condition. Here, the**C5:C13**cells represent theargument that refers to the*range**Quantity*, while the**“>25”**indicates theargument that returns the count of the values greater than*criteria**25*.**Output → 5**

**COUNTIF(D5:D13,”>1.5″) →**In this case, the**D5:D13**cells represent theargument that refers to the*range**Unit Price*, while the**“>1.5”**indicates theargument that returns the count of the values greater than*criteria**$1.5*.**Output → 4**

**5 + 4 → 9**

Eventually, the results should look like the figure shown below.

**Read More: **How to Use IF and COUNTIF Functions Together in Excel

## 2. Combining SUMPRODUCT and COUNTIF Functions

On the other hand, we can also apply the **OR **criteria by combining the **SUMPRODUCT** and **COUNTIF** functions to return the number of occurrences within the specified criteria i.e. *Unit Price less than $1.50 or greater than $2.00*. In this case, the **COUNTIF** function counts the number of values, and **the SUMPRODUCT function** returns the sum of the arrays.

📌 ** Steps**:

- In the first place, move to the
**D16**cell >> type in the expression given below.

`=SUMPRODUCT(COUNTIF(D5:D13,{"<1.50",">2.00"}))`

In this situation, the **D5:D13** cells represent the *Unit Price *column.

**Formula Breakdown:**

**COUNTIF(D5:D13,{“<1.50″,”>2.00″}) →**counts the number of cells within a range that meet the given condition. Here, the**D5:D13**cells represent theargument that refers to the*range**Unit Price*, while the**{“<1.50″,”>2.00″}**indicates theargument that returns the count of the values in between the range*criteria**$1.5*to*$2.00*.**Output → {5,1}**

**SUMPRODUCT(COUNTIF(D5:D13,{“<1.50″,”>2.00″})) →**becomes**SUMPRODUCT({5,1}) →**returns the sum of the products of corresponding ranges or arrays. Here,**{5,1}**is theargument which refers to the number of instances returned by the*array1***COUNTIF**function.**Output → 6**

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

**Read More: **How to Use COUNTIF and COUNTA Functions Together in Excel

## 3. Using IF and COUNTIF Functions

Alternatively, we can also combine the popular **IF** and **COUNTIF** functions to perform simple calculations based on the output returned by the **COUNTIF** function. Now, allow us to demonstrate the process in the steps below.

📌 ** Steps**:

- Initially, navigate to the
**D18**cell >> copy and paste the equation given below.

`=IF(COUNTIF($B$5:$B$13,B16),C16*D16,"Not Available")`

Specifically, the **B16**, **C16**, and **D16** cells point to *Avocados*, *25*, and *$2.00* respectively.

**Formula Breakdown:**

**COUNTIF($B$5:$B$13,B16) →**here, the**$B$5:$B$13**cells represent theargument that refers to the*range**Item Name*, while the**B16**cell indicates theargument that is*criteria**Avocados*.**Output → 1**

**IF(COUNTIF($B$5:$B$13,B16),C16*D16,”Not Available”) →**becomes**IF(1,C16*D16,”Not Available”) →**checks whether a condition is met and returns one value if**TRUE**and another value if**FALSE**. Here,**1**is theargument which prompts the*logical_test***IF**function to return**C16*D16**(argument) otherwise it returns*value_if_true***“Not Available”**(argument).*value_if_false***Output → $50.00**

📃 *Note: **Please make sure to use **Absolute Cell Reference** by pressing the F4 key on your keyboard.*

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

**Read More: **How to Use the Combination of COUNTIF and SUMIF in Excel

## 4. Utilizing SUM and COUNTIFS Functions

For one thing, we can utilize **the SUM function** in conjunction with **the COUNTIFS function** to count the number of times a value appears based on text criteria. In this situation, the **COUNTIFS** function computes the cells that match the condition (“*On Time*”,”*Delayed*“, or “*Not Available*”), and the **SUM** function returns the total count.

Considering the **Product Sales and Delivery Status** dataset shown in the **B4:D13** cells, which contain the **Product** name, **Sales** in *USD*, and delivery **Status **respectively.

📌 ** Steps**:

- To begin with, jump to the
**D16**cell >> insert the expression into the**Formula Bar**.

`=SUM(COUNTIFS($D$5:$D$13, {"On Time","Delayed"}))`

On this occasion, the **D5:D13** cells indicate the delivery *Status* column.

**Formula Breakdown:**

**COUNTIFS($D$5:$D$13, {“On Time”,”Delayed”}) →**counts the number of cells specified by a given set of conditions and criteria. Here, the**$D$5:$D$13**cells represent theargument that refers to the*criteria_range1**Status*, whereas the**“<>”**indicates theargument that represents the criteria*criteria1**On Time and Delayed*.**Output → {4,3}**

**SUM(COUNTIFS($D$5:$D$13, {“On Time”,”Delayed”})) →**becomes**SUM({4,3}) →**adds all the numbers in a range. Here,**{4,3}**is theargument which refers to the output returned by the*number1***COUNTIFS**function.**Output → 7**

Subsequently, the final output should look like the screenshot shown below.

**Read More: **Difference Between SUMIF and COUNTIF Functions in Excel

## 5. Employing DATE and COUNTIF Functions

Furthermore, we can also nest **the DATE function** inside the **COUNTIF** function to obtain a value that matches the given date criteria. Here, the **DATE** function stores the given date, while the **COUNTIF** function returns the number of matches for this date.

📌 ** Steps**:

- At the very beginning, enter the following into the
**D16**cell.

`=COUNTIF($D$5:$D$13,DATE(2022,1,15))`

For example, the **D5:D13** range of cells represents the *Date* column.

Consequently, this yields the output shown in the figure below.

**Read More: **How to Use SUMIF, COUNTIF and AVERAGEIF Functions in Excel

## 6. Applying COUNTIF, IF, INDEX, and MATCH Functions

Last but not least, by applying the **COUNTIF**, **IF**, **INDEX**, and **MATCH** functions, we can make a versatile formula to calculate the *Total Sales* based on the specified condition. In this case, the **COUNTIF** function checks if the condition holds, then the **INDEX-MATCH** functions extract the *Quantity* and *Unit Price *values, and lastly, the **IF** function returns the results based on the condition.

📌 ** Steps**:

- To start with, enter the
**D16**cell >> type in the following expression

`=IF(COUNTIF($B$5:$B$13, B16),INDEX($C$5:$C$13, MATCH(B16, $B$5:$B$13,0))*INDEX($D$5:$D$13, MATCH(B16, $B$5:$B$13,0)), "")`

Here, the **B16** cell points to the condition *Lemons*.

**Formula Breakdown:**

**MATCH(B16, $B$5:$B$13,0) →**returns the relative position of an item in an array matching the given value. Here,**B16**is theargument that refers to the*lookup_value**Condition Lemons*. Following,**$B$5:$B$13**represents theargument from where the value is matched. Lastly,*lookup_array***0**is the optionalargument which indicates the*match_type***Exact match**criteria.**Output → 5**

**INDEX($C$5:$C$13, MATCH(B16, $B$5:$B$13,0)) →**becomes**=INDEX($C$5:$C$13,5) →**returns a value at the intersection of a row and column in a given range. In this expression, the**$C$5:$C$13**is theargument which is the*array**Quantity*. Lastly,**5**is theargument that indicates the row location.*row_num***Output → 62**

**IF(COUNTIF($B$5:$B$13, B16),INDEX($C$5:$C$13, MATCH(B16, $B$5:$B$13,0))*INDEX($D$5:$D$13, MATCH(B16, $B$5:$B$13,0)), “”) →**becomes**IF(2,62*1.3,””) →**Here,**2**is theargument which prompts the*logical_test***IF**function to return**62*1.3**(argument) otherwise it returns*value_if_true***“” (Blank)**(argument).*value_if_false***Output → $80.60**

Ultimately, the results should appear in the image below.

## How to Use COUNTIF with Multiple Criteria in the Same Column in Excel

If you’re looking for ways to use the **COUNTIF** function with multiple criteria in the same column, then the following section answers this exact question. For instance, we want to find the number of *Sales* between ** $1000** and

**respectively. So, just follow along.**

*$2000*📌 ** Steps**:

- First, insert the equation in the
**D16**cell as shown below.

`=COUNTIF($C$5:$C$13, ">1000")-COUNTIF($C$5:$C$13,">2000")`

In this situation, the **C5:C13** array represents the *Sales* column.

Finally, after completing the above step, the output should be** 6** as shown in the screenshot below.

Here, we’ve skipped some of the relevant Examples of the COUNTIF function with multiple criteria in the same column, which you might explore if you want.

## How to Use COUNTIFS with Multiple Criteria in Different Columns in Excel

In a similar style, we can also employ the **COUNTIF** function with multiple criteria in different columns. In this scenario, we want to obtain the number of instances where the *Quantity* exceeds *50* or the *Unit Price* is above** $1.9**.Therefore, let’s see it in action.

📌 ** Steps**:

- In the first place, navigate to the
**D16**cell and insert the expression below.

`=COUNTIFS($C$5:$C$13,">50")+COUNTIFS($D$5:$D$13,">1.9")`

In the above expression, the **C5:C13** and **D5:D13** arrays refer to the *Quantity* and *Unit Price*.

Eventually, the final output should appear in the picture below.

Admittedly, we’ve skipped some relevant Examples of the COUNTIFS with multiple criteria in different columns which you may explore if you wish.

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

## Conclusion

In short, this article shows 8 effective methods for how to use nested **COUNTIF** in Excel. So, read the full article carefully and download the free workbook to practice. Now, we hope you find this article helpful and if you have any further queries or recommendations, please feel free to comment here.

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