## Method 1 – Nesting Two COUNTIF Functions

** Steps**:

- Go to the
**D16**cell >> enter the formula given below.

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

The **C5:C13** and **D5:D13** ranges refer to the *Quantity* and *Unit Price *columns.

**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″) →**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**

The results should look like the figure shown below.

## Method 2 – Combining SUMPRODUCT and COUNTIF Functions

** Steps**:

- Move to the
**D16**cell >> type in the expression given below.

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

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.**{5,1}**is theargument which refers to the number of instances returned by the*array1***COUNTIF**function.**Output → 6**

The results should look like the image shown below.

## Method 3 – Using IF and COUNTIF Functions

** Steps**:

- Navigate to the
**D18**cell >> copy and paste the equation given below.

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

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

**Formula Breakdown:**

**COUNTIF($B$5:$B$13,B16) →**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 if**FALSE**. Here,**1**is theargument, which prompts the*logical_test***IF**function to return**C16*D16**(argument) 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.*

Your output should look like the picture given below.

## Method 4 – Utilizing SUM and COUNTIFS Functions

** Steps**:

- Jump to the
**D16**cell >> insert the expression into the**Formula Bar**.

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

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. The**$D$5:$D$13**cells represent theargument that refers to the*criteria_range1**Status*, whereas the**“<>”**indicates theargument 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**

The final output should look like the screenshot shown below.

## Method 5 – Employing DATE and COUNTIF Functions

** Steps**:

- Enter the following into the
**D16**cell.

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

The **D5:D13** range of cells represents the *Date* column.

This yields the output shown in the figure below.

## Method 6 – Applying COUNTIF, IF, INDEX, and MATCH Functions

** Steps**:

- 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)), "")`

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.**B16**is theargument that refers to the*lookup_value**Condition Lemons*. Following,**$B$5:$B$13**represents theargument from where the value is matched.*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. 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,””) →****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**

The results should appear in the image below.

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

** Steps**:

- Insert the equation in the
**D16**cell as shown below.

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

The **C5:C13** array represents the *Sales* column.

The output should be** 6,** as shown in the screenshot below.

We 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

** Steps**:

- Navigate to the
**D16**cell and insert the expression below.

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

The **C5:C13** and **D5:D13** arrays refer to the *Quantity* and *Unit Price*.

The final output should appear in the picture below.

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

**Download Practice Workbook**

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