**COUNTIF** and **SUMIF** are two of the most effective and versatile functions in Excel. The** COUNTIF** and **SUMIF **functions are attributed to conditional counting and summation respectively. These functions perform the task of **SUM**, **COUNT**, and **IF** functions. However, sometimes we need to use them together to get our desired results. In this article, we will show how to use **COUNTIF** and **SUMIF** functions in combination.

## How to Use the Combination of COUNTIF and SUMIF in Excel: 2 Examples

In this article, we will discuss **2** instances of using the **COUNTIF** and **SUMIF** functions in combination. In the first example, we will find out the average price of the products by combining the functions. Then, we will determine the number of products that have sales values less than a certain amount. The following image represents the dataset that we will use to show the application of **COUNTIF **and **SUMIF **functions together.

### 1. Determining Average Price

**The COUNTIF function** is a statistical function in **Excel**. It counts the number of cells that match a particular criterion. It returns a number as its output. **The SUMIF function** too belongs to the category of statistical functions. It will return the summation of cell values that fulfill a particular condition. In this method, we will use these functions to determine the average price of the products.

**Steps:**

- Firstly, select the
**F5**cell and write down the following formula,

`=SUMIF(B5:B15,"Apple",C5:C15)/COUNTIF($B$5:$B$15,"Apple")`

- Then, hit
**Enter**. - As a result, we will get the average price of the item.

- Then, lower the cursor down to
**AutoFill**the rest of the cells.

🔎 **Formula Breakdown****:**

**SUMIF(B5:B15,”Apple”,C5:C15):****The SUMIF function**will go through the**B5:B15**range and look from**“Apple”**. Then, it will sum all the prices associated with**“Apple”**in the range**C5:C15**.In this case the summation will be**16**.**COUNTIF($B$5:$B$15,”Apple”):****The COUNTIF function**will scan through the cells in the range**B5:B15**and return the number of occurrence of**“Apple”**in that cell range. Here, it will be**4**.**SUMIF(B5:B15,”Apple”,C5:C15)/COUNTIF($B$5:$B$15,”Apple”):**This one is simply the formula of average price. We have the total number of apples over the total price of the apples. In this instance, it will be**$16**by**4**or**$4**.

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

### 2. Using COUNTIF and SUMIF with Condition

In this instance, we will apply an inequality condition in our formula which consists of the **COUNTIF** and the **SUMIF** functions. We will find out the number of items that have a total price less than **$18**.

**Steps:**

- To begin with, select the
**E5**cell and write the following formula down,

`=SUMPRODUCT((SUMIF(B5:B15,B5:B15,C5:C15)<18)/COUNTIF(B5:B15,B5:B15))`

- Then, hit the
**Enter**button. - Consequently, we will get the number of items that have a total price less than
**$18**.

🔎 **Formula Breakdown:**

**COUNTIF(B5:B15,B5:B15):**This will return an array of values.**The COUNTIF function**will look for the number of occurrence of**“Apple”**,**“Mango”**and**“Orange”**as it moves from cell**B5**to**B15**and for each value in each cell it will look for number of occurrences of that value in the**B5:B15**range and return the number. The result would be something like this :**{4;4;4;3;4;3;4;3;4;4;4}**.**SUMIF(B5:B15,B5:B15,C5:C15)<18):**At first,**the SUMIF function**will look for**“Apple”**,**“Mango”**and**“Orange”**in each cell. For example, in the**C5**cell, it will have**Apple**. So it will sum all the prices of**Apple**from the**C5:C15**range and return**$16**. Then. it will sum for**Mango**and return**$19.5**and then again it will sum for Apple and return**$16**and it will do the same as it move from**B5**to**B15**and finally return an array like this :**{16;19.5;16;14.8;19.5;14.8;16;14.8;16;19.5;19.5}**. Then, it will evaluate the values if they are less than**$18**or not. Then it will return an array of**True**and**False**like this:**{True;False;True;True;False;True;True;True;True;False;False}**.**SUMPRODUCT((SUMIF(B5:B15,B5:B15,C5:C15)<18)/COUNTIF(B5:B15,B5:B15)):**Finally, the**True**values will be replaced by**1**and the**False**values with**0**. Then, these array with**1’s**and**0’s**will be divided by the array returned by**the COUNTIF function**and will return an array like this:**{0.25;0;0.25;0.333333;0;0.333333;0.25;0.333333;0.25;0;0}**. Finally,**the SUMPRODUCT function**will sum these values and will return**2**.

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

**Download Practice Workbook**

You can download the practice workbook here.

## Conclusion

In this article, we combined the **COUNTIF** and the **SUMIF** functions in a formula. Since, individually, the functions are very versatile, the combination of them allows users to perform different calculations in a robust way. If you have any questions regarding this essay, feel free to let us know in the comments.

## Related Articles

- How to Use Nested COUNTIF Function in Excel
- How to Use COUNTIF and COUNTA Functions Together in Excel
- How to Use IF and COUNTIF Functions Together in Excel.

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

How could you perform the same count of records with SUMPRODUCT((SUMIFS)/(COUNTIF)? Trying count how many sumifs records (not sum value) with two criteria vs one. The return I am looking for is of the total sum value using a range (>0 & <10000), count how many excel finds to total the sum value produced.

Hello

BRIAN,Thanks for your comment. You can apply two criteria by using

the SUMIF functiontwice in the formula. The formula is:`=SUMPRODUCT((SUMIF(B5:B15,B5:B15,C5:C15)<10000)*(SUMIF(B5:B15,B5:B15,C5:C15)>0)/COUNTIF(B5:B15,B5:B15))`

This will count the number of products whose total sum value is greater than

0and less than10000.Regards

Mahfuza Anika EraExcelDemy