Array Formulas

From http://www.ozgrid.com/Excel/arrays.htm

Array Formula Rules:

Before we show some examples of array formulas it is important to know 4 fundamental rules.

• Each argument within an array must have the same amount of rows and columns.
• You must enter an array by pushing Ctrl+Shift+Enter.
• You cannot add the {} (braces) that surround an array yourself, pushing Ctrl+Shift+Enter will do this for you.
• You cannot use an array formula on an entire column.

Pet Shop Example:

Suppose you have 5 Columns of data each with 200 rows.
Column A is used to keep track of the sex of each dog sold i.e. Male or Female
Column B is used to keep track of the breed of the dogs sold.
Column C is used to keep track of the age of the dogs sold.
Column D is used to keep track whether the dog is sterilized or not i.e. Yes or No
Column E is used to keep track of the cost of the dog sold.

• To count the number of male Poodles sold:
  =SUM(($A$2:$A$200="Male")*($B$2:$B$200="Poodle"))
• To count the number of male Poodles sold over 3 years old:
  =SUM(($A$2:$A$200="Male")*($B$2:$B$200="Poodle")*($C$2:$C$200>2))
• To get the total cost of male Spaniels sold:
  =SUM(IF($A$2:$A$200="Male",IF($B$2:$B$200="Spaniel",$E$2:$E$200,0),0))
• To find out the average age of male dogs sold:
  =AVERAGE(IF($A$2:$A$200="Male",$C$2:$C$200))
• To find out the average cost of male dogs sold over 2 years old:
  =AVERAGE(IF($A$2:$A$200="Male",IF($C$2:$C$200>2,$E$2:$E$200)))
• To find out the Minimum age of dogs sold that are sterilized:
  =MIN(IF($D$2:$D$200="Yes",$C$2:$C$200))

All the above formulas must be entered with Ctrl+Shift+Enter

TIP: If you are having problems writing an array formula to sum your totals then use the Conditional sum wizard, Tool>Wizard>Conditional sum. If you don't see it then you will need to add it via Tools>Add-ins>Conditional sum wizard.