AVERAGEIF and AVERAGEIFS in Google Sheets - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the AVERAGEIF function do in Google Sheets?

AVERAGEIF calculates the average (mean) of numbers in a range that meet a single condition.

beginner

How is AVERAGEIFS different from AVERAGEIF?

AVERAGEIFS calculates the average of numbers in a range that meet multiple conditions at the same time.

beginner

Write the syntax of AVERAGEIF.

AVERAGEIF(range, criterion, [average_range])<br><br>- range: cells to check the condition<br>- criterion: condition to meet<br>- average_range: cells to average (optional, if different from range)

beginner

Write the syntax of AVERAGEIFS.

AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])<br><br>- average_range: cells to average<br>- criteria_range1: first range to check<br>- criterion1: first condition<br>- Additional pairs for more conditions

beginner

Example: How to find the average score of students who scored more than 70 using AVERAGEIF?

Use: =AVERAGEIF(B2:B10, ">70")<br>This averages scores in B2:B10 where the score is greater than 70.

What does the AVERAGEIFS function allow you to do that AVERAGEIF does not?

A Sum numbers based on one condition

B Average numbers without any condition

C Count how many cells meet a condition

D Average numbers based on multiple conditions

AVERAGEIFS lets you average numbers that meet several conditions at once, unlike AVERAGEIF which only uses one condition.

Which argument is optional in the AVERAGEIF function?

A criterion

B average_range

C range

D none

The average_range is optional; if omitted, the function averages the cells in the range argument.

If you want to average values in C2:C10 where A2:A10 equals "Yes" and B2:B10 is greater than 50, which function should you use?

A AVERAGEIF

B SUMIF

C AVERAGEIFS

D COUNTIF

AVERAGEIFS supports multiple conditions, so it fits this need.

What symbol is used in criteria to mean 'greater than' in AVERAGEIF?

A >

B <

C =

D !

The > symbol means 'greater than' in criteria.

True or False: AVERAGEIF can average cells based on text conditions like "apple".

A True

B False

C Only with numbers

D Only with dates

AVERAGEIF can use text conditions to average cells where the condition matches text.

Explain how you would use AVERAGEIF to find the average sales greater than $1000 in a list.

Think about which cells hold sales and what condition to write.

Describe a situation where AVERAGEIFS is better than AVERAGEIF and how you would write the formula.

Use more than one condition to filter data.