Use AVERAGEIF Google Sheets function to average a range of numbers, based on a single criterion, and use AVERAGEIFS to average a range of numbers, based on two or more criterion. For example, you may want to use AVERAGEIF Google Sheets formula, if you wish to find the average price of a mobile phone, available at many e-commerce sites, with 64GB storage space.
When should you use AVERAGEIFS? This function helps you to average a range of numbers, based on multiple criterion. Let's consider the above example again. Say you want to find the
average price of a mobile phone, offered by multiple sellers, with 64GB storage space, triple camera, and 4GB RAM. The AVERAGEIFS function in Google Sheets helps you accomplish the task.
Here is the format of AVERAGEIF Google Sheets function:
=AVERAGEIF(criterion_range, criterion, [average_range])
A function, like the above, and formula in Google Sheets always starts with = (equal-to symbol).
AVERAGEIF is the name of the function.
criterion_range, for example, C2:C10, should contain the criterion.
criterion, for example, "64GB", is the condition that criterion_range should satisfy.
average_range is optional and contains the numbers to average. If you do not use this attribute, Google Sheets will average the numbers in criterion_range.
Here is the format of AVERAGEIFS Google Sheets:
=AVERAGEIFS(average_range, criterion_range1, criterion1, [criterion_range2, criterion2], ...)
AVERAGEIFS is the name of the function.
average_range is the range you wish to average.
criterion_range1 is the first criterion range.
criterion 1 is the first criterion, and so on.
Lets look at an example one each for AVERAGEIF Google Sheets and AVERAGEIFS functions:
Here is a sample spreadsheet with price of mobile phones.
A B C D
E-Comm Site Cameras Hard Disk Price
(in Rupees)
----------- ------- --------- -----
1 E-comm1 3 64GB 11,000.00
2 E-comm2 3 64GB 12,500.00
Say you want to find the average price of triple-camera mobile phones available at two e-commerce sites.
The AVERAGEIF Google Sheets function for the above task is:
=AVERAGEIF(B1:B2, C1, D1:D2)
Assume that you want to get the average price of mobile phones with triple-camera and 64GB storage.
The AVERAGEIFS function, with two criterion, that gives you the average price is:
=AVERAGEIFS(D1:D2, B1:B2, B1, C1:C2, C1)
To recap, use the AVERAGEIF Google Sheets function to find the average of numbers in a range, based on one criterion. Use the AVERAGEIFS function to get the average of numbers, based on
a criteria.
Take a look at this tutorial, which gives the steps to use AVERAGEIF and AVERAGEIFS Google Sheets function with an example.
