For convenience only,data(C5:E16) andgroup(B5:B16) arenamed ranges.
InLegacy Excel, you’ve got the option to use solution #3 or #4.
AVERAGEIFS won’t work
You might be tempted to solve this problem with theAVERAGEIFS function.
After all, it seems to fit the bill.
In fact, we can easily use AVERAGEIFS to calculate an average for a given group onone monthof data.
The reason is thatAVERAGEIFSexpectsaverage_rangeto be the same size ascriteria_range.
You may not like formula errors, but at least they tell you something is wrong.
AVERAGE with IF
The FILTER function is a newer function that does not exist inLegacy Excel.
When a value doesn’t match, IF returns FALSE for corresponding values indata.
All other values become FALSE and are ignored by the AVERAGE function.
Both formulas above arearray formulasand must be entered with control + shift + enter in older versions of Excel.
In the current version of Excel, which supportsarray formulas natively, the formulas will “just work”.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
More than one condition can be tested by nesting IF functions.
Related videos
FILTER function basic example
Boolean operations in array formulas
