Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use SUMIF, COUNTIF and AVERAGEIF Functions in Excel

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.

Dataset to Use SUMIF, COUNTIF, AVERGAEIF FunctionsDealing 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

SUMIF Function in Excel

🔓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.

Excel SUMIF Function

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.

Excel COUNTIF Function

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.

Excel COUNTIF Function

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


Similar Readings


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.

Excel AVERAGEIF Function

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.

Excel AVERAGEIF Function

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.

SUMIF, COUNTIF, AVERAGEIF Functions


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.

Practice Excel SUMIF, COUNTIF, AVERAGEIF Function


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

Rafi

Rafi

Hey there! I am Md. Rafiul Hasan. Currently I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our worktime and made it easy for us to quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo