Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
AVERAGEIF vs AVERAGEIFS: one or many conditions in Excel and Sheets
Excel & Google Sheets
=AVERAGEIFS(C2:C7,B2:B7,"West",A2:A7,"Q1")Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Set up a table: column A = Quarter (e.g. Q1/Q2), column B = Region (e.g. East/West), column C = Sales amount.
- 2To average sales in the West region only, use AVERAGEIF: =AVERAGEIF(B2:B7,"West",C2:C7). Note the argument order: criteria_range, criteria, average_range.
- 3To average sales for West in Q1 only, switch to AVERAGEIFS: =AVERAGEIFS(C2:C7,B2:B7,"West",A2:A7,"Q1"). Note: AVERAGEIFS puts the average_range FIRST.
- 4Add more criteria pairs after the first pair — AVERAGEIFS accepts up to 127 condition pairs.
- 5Use wildcard characters (*,?) in text criteria and comparison operators in quoted strings (e.g. ">5000") for flexible matching.
Tips
- The most common mistake: AVERAGEIF has the average_range last; AVERAGEIFS has it first. Mixing up the order causes wrong results with no error.
- AVERAGEIFS returns #DIV/0! when no rows match all criteria — wrap it in IFERROR(AVERAGEIFS(...),"No data") to handle empty results gracefully.
- Both functions ignore blank cells and text in the average_range when computing the mean, so hidden or blank rows do not distort the average.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Can I use AVERAGEIF with a numeric threshold condition?
Yes. Use a comparison string as the criteria: =AVERAGEIF(C2:C7,">5000") averages all values in C2:C7 greater than 5000 (criteria_range and average_range are the same column).
Is there a performance difference between AVERAGEIF and AVERAGEIFS?
Negligible on typical datasets. AVERAGEIFS with one criterion pair is equivalent to AVERAGEIF and equally fast.
Do both work in Google Sheets?
Yes. Both AVERAGEIF and AVERAGEIFS are fully supported in Google Sheets with identical syntax to Excel.