Creating Arrays for Excel Formulas

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