What is an Array?
The Dictionary Definition is"A rectangular arrangement of quantities in rows and columns, as in a matrix"
Simple put this means working with more than one cell at once.
How do I enter an Array?
Array formula are always surrounded by{} braces, you can't type these and press enter, otherwise you will get an error or the wrong answer. To enter an array formula you must press CTRL+Shift+Enter, this will put the braces in for you.
Why are Array Formulas Useful?
Arrays allow you to do more than you can with SUMIF and COUNTIF. Array formulas are great for working with multiple criteria.
How do Array Formulas Work
Arrays work by looking at the first element of each array in turn then the second element of each array in turn....
Example
If we want to know how many Computers were sold in the North region we would use the following.
{=SUM((A2:A11="North")*(B2:B11="Computer")*(C2:C11))}
In the formula above the first part looks at
A2 and if it's equal to "North" it gives it a value of 1,
it then Looks at
B2 and if it's equal to "Computer" then it gives it a value of 1,
it then multiplies this by C2.
Giving the equation 0*0*10=0.
It does this for every line in turn and then adds them together and gives you the answer 9.
| |
A |
B |
C |
| 1 |
Region |
Item |
Sold |
| 2 |
South |
Computer |
10 |
| 3 |
South |
Printer |
12 |
| 4 |
North |
Computer |
8 |
| 5 |
North |
Printer |
9 |
| 6 |
East |
Monitor |
5 |
| 7 |
North |
Printer |
17 |
| 8 |
South |
Computer |
6 |
| 9 |
South |
Printer |
4 |
| 10 |
South |
Monitor |
9 |
| 11 |
North |
Computer |
1 |
Important things about Array formulas
All arrays must be the same size i.e. have the same number of columns and rows as each other.
Array Formula Sample File click here to download
|
