Tom’s Tutorials For Excel: Averaging Multiple Criteria With Arrays
Array formulas are useful when calculations involve multiple criteria. In the following pictures, three examples show increasingly complex array formulas that, when you view them in order, can help you understand how array formulas are structured.
Recall, array formulas are applied to a cell by pressing Ctrl+Shift+Enter, not just Enter. The curly braces are entered automatically by Excel; you do not enter them yourself.
In Picture #1, average Quantity of 269 is returned in cell G5 for the single criteria of East Region.
The formula is =SUM((A2:A20="East")*(D2:D20)).
Picture #1
In Picture #2, average Quantity of 59 is returned in cell G11 for two criteria (West and Wombats Co).
The formula is =SUM((A2:A20="West")*(B2:B20="Wombats Co.")*(D2:D20)).
Picture #2
In Picture #3, the average Quantity of 104 is returned in cell G18 for three criteria of North, Widgets, Inc., and Premium Widgets. Notice in this example the formula refers to the cell references holding the criteria items.
That formula is =SUM((A2:A20=F15)*(B2:B20=F16)*(C2:C20=F17)*(D2:D20)).
Picture #3
it does not appear that these formulas are averaging, they are just summing (well not “just” summing, because the array portion is still working, but they are still summing, not averaging).
Thanks for telling me about this. I’ll make the corrections.
well … i understand that these formulas for Summing not averages !!!