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