Rafiul Hasan
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.
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 criteria are to be assigned and the sum_range is the range in which value is to be calculated. Criteria may be in the form of numbers, cell references, text, or output of other functions.
💡 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 criteria are assigned and from which cells will be counted. The criteria may be in 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 criteria denote the condition for the range. The range from which the average value is to be calculated is the 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
