What is an Array?
Array formulas are powerful formulas that enable you to perform complex calculations that often can’t be done with standard worksheet functions. They are also referred to as "Ctrl-Shift-Enter" or "CSE" formulas, because you need to press Ctrl+Shift+Enter to enter them. You can use array formulas to do the seemingly impossible, such as performing calculations on a range of cells certain cells meet the requirements.
When to use an Array?
The most common use of an array is when you have a sheet of data, and you want to search one column for a value to then preform a calculation on other columns. Then have all the results added up to get a total.
How do i use an Array?
Lets use an example of Duct Data. We have an exported sheet of data with the below information. For this example, we want to get total joint cost of TDC.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item Name | Joint | Quantity | Joint Cost |
| 2 | Duct | TDC | 2 | $13.00 |
| 3 | Tap In | S&D | 1 | $14.00 |
| 4 | Duct | TDC | 1 | $12.00 |
| 5 | Duct | DuctMate | 4 | $14.00 |
| 6 | Duct | DuctMate | 1 | $18.00 |
| 7 | Elbow | S&D | 2 | $22.00 |
| 8 | Elbow | TDC | 3 | $21.00 |
| 9 | Transition | TDC | 1 | $7.00 |
| 10 | Duct | S&D | 3 | $5.00 |
| 11 | End Cap | TDC | 2 | $22.00 |
| 12 | Elbow | TDC | 1 | $4.00 |
| 13 | Transition | TDC | 1 | $10.00 |
| 14 | End Cap | DuctMate | 3 | $7.00 |
For this we will need to use an array formula. To do so follow these steps:
- Click into the cell you wish to have the total displayed
- Type out the formula as you normally would using Sum(IF statement
- example: =SUM(IF(B:B="TDC",C:C*D:D,0))
- While the focus is still in the formula bar or cell, press Ctrl+Shift+Enter to finish the formula
Now click back into that cell and look at the formula bar. Notice there are symbols before and after the formula "{ }". Those curly brackets indicate it is an Array formula.
However, you cannot just type those in, you must use Crtl+Shift+Enter when finishing the formula in order for the cell to be formatted properly.
So what happens if i don't use an array?
If you do not use an array formula and type in the same formula, which was =SUM(IF(B:B="TDC",C:C*D:D,0)), then excel will ONLY check the first row in the formula. You must use an array to continue through all rows within the data set.
