Three average functions that each answer a different question — here's exactly when to reach for each one.
=AVERAGE(B2:B100)
-- Average of all numbers in B2:B100
-- Ignores blank cells and text (doesn't treat blanks as zero)=AVERAGEIF(range, criteria, [average_range])
=AVERAGEIF(A2:A100, "North", B2:B100)
-- Average of B values where A = "North"
=AVERAGEIF(B2:B100, ">1000")
-- Average of values in B that are greater than 1000=AVERAGEIFS(avg_range, criteria_range1, criteria1, ...)
=AVERAGEIFS(C2:C100, A2:A100, "North", B2:B100, "Active")
-- Average of C where A = "North" AND B = "Active"AVERAGEIF has the average range as the third argument: =AVERAGEIF(criteria_range, criteria, avg_range). AVERAGEIFS puts the average range first: =AVERAGEIFS(avg_range, criteria_range, criteria). This inconsistency trips up almost everyone.
Use AVERAGEIFS by default — it handles one or multiple conditions and replaces AVERAGEIF entirely. Just remember the average range goes first in AVERAGEIFS.
ExcelPro has 880+ hands-on exercises across 9 tracks — type real formulas, get instant feedback.
Start practising free →