How to Use the AVERAGEIF Function in Excel | Calculates the Average of Cells that Meet One Criteria

In this video, we will teach you how to use the AVERAGE IF function in Excel

The AVERAGE IF function allows you to calculate the mean of a range based on a given criteria. So over here, we have a student marksheet, and let’s say we want to find out the average test score for Class A and B. For that, we will be using the Average IF function and use the criteria we have defined here, under the average heading, to determine the average test score of that class.

So let’s move to the cell here and type in “equals to” followed by Average IF and brackets open. First, we have to define the range. For that, let’s select the cells with the class type defined in column D here.

Let’s enter a comma and next we will define the criteria. In this case, we want to get the average score of Class A, so let’s use this cell as a reference.

After that, let’s enter a comma and now we will select the average range. By doing this, whenever the formula finds a matching value from the test range we have defined here, it will determine the average from the corresponding cells. So, let’s select all the score values here in column C.

You can select the range and average range in the formula and press F 4 on the keyboard to make it into an absolute reference. This will make it easier for copying and pasting the formula for the criteria below.

Once you hit enter, the formula will automatically return the average test scores for Class A. Now let’s copy and paste the formula below, and since we didn’t make the criteria as an absolute reference in the formula, it will automatically pick up the criteria value in the cell here and return the average test score for Class B.

That’s all!

❓💬 Is there any Excel function you would like to know more about? Let us know in the comments below.

#HowTech #Excel
--------------------------------------------------------------------------------------------------------------

[clairez.]

Thanks for this video. It's very clear and helpful.

[patelnamrata]

If the first 5 students expect to get the Final Average of 95, what would their Final Tests need to be. Would the What function could I use for this?

[joeunyah]

Please, how can I calculate average sales from a category of different products? Let's say Technology, furniture, and office supplies of 10 customers
Calculate the average sales of the customers from the Furniture category using an IF statement in the "Average Furniture" column.
Calculate the number of customers from the Technology category using an IF statement in the "Count Technology" column.
Please, can you demonstrate an example of the above?