Dealing with a set of data consisting of texts as well as numerical values of a company or an organization, it is a common scenario to feel the necessity of summarizing the data for a better observation about the upliftment of the company. Applying a set of functions paves the way to implement this task. This tutorial discusses the applications of the three most used functions for summarizing data for better observation. You will learn about the use of **SUMIF**, **COUNTIF**, and **AVERAGEIF **functions in Excel.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice book from the link below.

## Applications of SUMIF, COUNTIF and AVERAGEIF Functions in Excel

Let’s say, we have a dataset of some companies selling different types of products (i.e. **Glass**, **Plastic**, **Steel**,… etc.) and the selling price of these products.

Dealing with this type of data also include focusing on specific types of item or criteria assigned to the princess and creating a summary of the assigned criteria.

In this section, We will try to demonstrate to you the use of **SUMIF**, **COUNTIF**, and **AVERAGEIF **functions in Excel. Applying these **3** functions will help you predict specific items of the dataset.

### 1. Application of SUMIF Function in Excel

#### ⏩ **Overview of SUMIF Function**

The **SUMIF **function is used for summing a range of numerical values based on criteria assigned over a specific range. The *syntax *of this function is:

`=SUMIF (range, criteria, [sum_range])`

Here, in the function, ** range **means the cells in which

**are to be assigned and the**

*criteria***is the range in which value is to be calculated.**

*sum_range***may be in the form of numbers, cell references, text, or output of other functions.**

*Criteria*#### 💡 **Application**

For the dataset we are dealing with, multiple companies are selling **Glass **at different prices. We want to find out the **Total Price **of the **Glass** using the **SUMIF **function**. **As this function sums value on a given criterion, we also want to calculate the total prices of all items greater than **$2000**.

**Steps**:

- First of all, select a cell where you want to find out the
**Total Price**of**Glass**. Now type the formula stated below in that cell.

`=SUMIF(C5:C20,"Glass",D5:D20)`

Here,

**C5:C20**= The range on which criteria to be assigned**Glass**= Criteria (As we only want the total price of**Glass**)**D5:D20**= sum will be calculated from this range

**🔓****Formula Unlocking**

The function looks for the criteria: **Glass** in the range **C5:C20**. Then it finds the cells where the criteria are met and then sums the cell from the range **D5:D20**.

- Then, press
**ENTER**, and this cell will calculate the**Total price of Glass**.

This formula has used descriptive or text criteria.

If you want to assign a numerical criterion to your dataset, Excel also keeps the door open for you. Let’s say, we want to calculate the **Total **of the prices which are **Greater than $2000**. Apply the following formula to get the sum of prices exceeding **$2000**.

`=SUMIF(D5:D20,">2000")`

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

### 2. Use of COUNTIF Function in Excel

#### ⏩ **Overview of COUNTIF Function**

The objective of the **COUNTIF **function is to count the number of cells with a range based on a given criterion.

The *syntax *of this function is:

`=COUNTIF(range, criteria)`

Here, the ** range **is a compulsory argument where

**are assigned and from which cells will be counted. The criteria may be in**

*criteria***text**or

**numerical**value.

#### 💡 **Application**

The same set of data will be used now for demonstrating the application of the **COUNTIF** function. In the datasheet, you may notice that products are found repeat for different companies. If you want to find the number of companies that sell a specific product (i.e. **Glass**), you should count how many times that product occurred in the data set.

For counting the total number of companies selling a similar product (i.e. **Glass**), apply the formula below.

`=COUNTIF(C5:C20,"Glass")`

Here,

**C5:C20**= The range on which criteria to be assigned**Glass**= Criteria (As we only want the total price of**Glass**)

The function counts the cells where this criterion is met and then returns it to the formulated cell.

You can also count specific data on a **Numerical Criterion** like the number of sales that are greater than **$2000**. Just apply the formula in the form stated below.

`=COUNTIF(D5:D20,">2000")`

The criteria is set “**>2000**”.So, the function counts all the cells in the range **D5:D20 **and returns the counted result.

**Read More: Excel COUNTIF to Count Cells Greater Than 1 (2 Examples)**

**Similar Readings**

**How to Use COUNTIF for Non Contiguous Range in Excel****Apply COUNTIF Function in Multiple Ranges for Same Criteria****How to Use Excel COUNTIF Between Time Range (2 Examples)****Excel COUNTIF Function to Count Cells Greater Than 0****How to Use Excel COUNTIF That Does Not Contain Multiple Criteria**

### 3. Appliance of AVERAGEIF Function in Excel

#### ⏩ **Overview of AVERAGEIF Function**

The **AVERAGEIF **function is a special type of Excel function that is used for calculating the arithmetic mean or the average of some numerical values based on a specified condition or criterion. The *syntax *of this function is:

`=`

`AVERAGEIF(range, criteria, [average_range])`

The first argument ** range** describes the cells where the condition is to be applied and the

**denote the condition for the range. The range from which the average value is to be calculated is the**

*criteria***.**

*average_range*#### 💡 **Application**

Previously we have counted the number of companies (**4**) selling a similar product: **Glass **and the total price for this product was also calculated (i.e. **$7400**). Dividing the total price by number will get you the average value. But if you want to calculate the average value directly from the dataset, apply the following formula.

`=AVERAGEIF(C5:C20,"Glass",D5:D20)`

The function meets the criterion **Glass **in the range **C5:C20 **and extracts value from the range **D5:D20**. After that, it calculates the averages of the extracted value.

The** AVERAGEIF **function takes the criteria into consideration. The criteria may be of numerical condition also. For example, if you want to calculate the average of some numbers that are greater than (i.e. **>$2000**), use the following formula.

`=AVERAGEIF(D4:D19,">2000")`

It finds all the cells from the range **D4:D19 **and calculates the average of that values.

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

## Summary

You have seen the uses and applications of **SUMIF**, **COUNTIF**, and **AVERAGEIF **functions. If you want to summarize one value in a single worksheet, you’re welcome. Just apply the all formula and find out the values you need. The image below is a visual representation of the function we have previously.

## Practice Section

Here I am providing you with an extra sheet so that you can practice yourself. Just input data in the mentioned area and you will get the result.

## Conclusion

In this article, I have tried to show you some uses and applications of **SUMIF**, **COUNTIF**, and **AVERAGEIF **functions in Excel. Hope this tutorial has helped you to some extent. Don’t forget to share them in the comment box if you have any questions or feedback regarding this article. For more queries, kindly visit our website ** ExcelDemy**. Have a great day!

## Related Articles

**How to Use Nested COUNTIF Function in Excel (6 Suitable Ways)****VBA COUNTIF Function in Excel (6 Examples)****How to Apply Excel COUNTIF with Pivot Table Calculated Field****COUNTIF Excel Example (22 Examples)****How to Use COUNTIF Between Two Numbers (4 Methods)****Excel COUNTIF with Greater Than and Less Than Criteria**