The AVERAGEIF, AVERAGEIFS guide
=AVERAGEIF(B2:B13, "Billing", D2:D13) averages the hours on the Billing rows. =AVERAGEIFS(D2:D13, B2:B13, "Tech", C2:C13, "High") takes several conditions, and the range to average comes first.
Syntax
=AVERAGEIF(range, criteria, [average_range])
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
The same swap as SUMIF and SUMIFS: in AVERAGEIF the numbers come last, in AVERAGEIFS first. AVERAGEIFS works for one condition too, so some people use only that.
Empty cells are left out
An open ticket with no hours yet doesn't count: AVERAGEIF averages the numbers it finds, not the rows that match. A 0 is different. It's a number, so a ticket entered as 0 hours pulls the average down. Leave a cell empty when there's no value yet.
That's also why SUMIF(…)/COUNTIF(…) can be wrong: COUNTIF counts the open ticket, SUMIF adds nothing for it, and the total is divided by one row too many.
#DIV/0!
When no row matches, there's nothing to average, and the result is #DIV/0!. Usually it's a typo in the condition ("Biling") or a range pointed at the wrong column. If "no rows" is a real possibility, wrap it: =IFERROR(AVERAGEIF(…), "No tickets").
Don't average the averages
The average of two group averages isn't the average of all the rows unless the groups are the same size. Average the rows themselves with AVERAGEIFS.
Common mistakes
- AVERAGEIF's order in AVERAGEIFS. The hours go first.
- No average range in AVERAGEIF. It averages the team names and gives #DIV/0!.
- One condition when the task has two. You get the average for the whole team.
FAQ
- What is the difference between AVERAGEIF and AVERAGEIFS?
- AVERAGEIF takes one condition and puts the range to average last. AVERAGEIFS takes one or more and puts the range to average first.
- Why does AVERAGEIF return #DIV/0!?
- No row matched the condition, so there is nothing to average. Check the spelling and the ranges.
- Does AVERAGEIF ignore blank cells?
- Yes. Empty cells in the average range are left out. Zeros are counted.
- How do I average with multiple criteria in Excel?
- Use AVERAGEIFS: =AVERAGEIFS(D2:D13,B2:B13,"Tech",C2:C13,"High").



































































