Tom’s Tutorials For Excel: Averaging Multiple Criteria With Arrays

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

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
3 comments on “Tom’s Tutorials For Excel: Averaging Multiple Criteria With Arrays
  1. Becky says:

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

  2. Hussein Korish says:

    well … i understand that these formulas for Summing not averages !!!

Leave a Reply

Your email address will not be published.

*