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
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