In this tutorial we are going to look at the different ways to structure your criteria when using the AVERAGEIFS function in Excel, along with the basic difference between AVERAGEIF & AVERAGEIFS.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Here I have a data range that I want to average the values in column “D” based on various criteria:
I normally would use AVERAGEIFS, even when I have only a single criteria, because it is structured more logincally than the AVERAGEIF function. The syntax for each is as follows:
=AVERAGEIF(range, criteria, [average_range])
=AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, ….)
Note that for the AVERAGEIF function, the first argument is “range” but it doesn’t clearly define what range that is. It turns out that it needs to be the criteria range, but that’s not easily determined.
For the AVERAGEIFS function, the structure is more logical. You identify the range you want to average, then the range of the first criteria and that criteria, the range of the second criteria and that criteria, etc. That’s why I tend to use AVERAGEIFS even when I only have one criteria.
So, using the AVERAGEIFS function for the next few examples, let’s go through how to structure the criteria.
If I want to average column D based on what rows in column “A” contain the number 2, the formula would be:
$D$2:$D$49 is the range I want to average
$A$2:$A$49 is criteria_range1
2 is criteria1
Since the criteria is a value, we don’t have to modify it with any punctuation, just enter a 2.
If I want to average column D based on the word “Two” in column B, I would use the following formula:
Notice that when using text, you need to put that in double quotes.
If I want to average column D based on a specific date that I will manually enter, say January 11th, I would use this formula:
Note that even though a date is a number, Excel requires you to put it in double quotes.
If I wanted the average to be based on the dates that are greater than or equal to January 11th, I would use the following:
Here I need to put the operation and the date inside the double quotes.
For the next two examples, I want to reference dates that are in other cells:
Cell H3 contains 1/15/2017, and cell I3 contains 2/9/2017.
The formula to average the values in column D that are greater than or equal to the date in cell H3 would be:
Notice that here I need to put the operation in double quotes then concatenate that to the cell reference using the ampersand. If I tried to put the whole criteria in double quotes like this – “>=H3”, I would get a #DIV/0! error.
Finally, if I wanted to average the values that fall between the two dates, I would then have two criteria ranges and two criteria, and that formula would be:
…again having to put the operations in double quotes and concatenating them to the cell reference with an ampersand.
$D$2:$D$49 – average_range
$C$2:$C$49 – criteria_range1
“>=”&H3 – criteria1
$C$2:$C$49 – criteria_range2
“< =”&I3 – criteria2
I hope this is clear and useful for you. Take a few minutes to watch the video for a more detailed walk-through of creating these formula.
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!