Excel Array Formula

Knowing Excel array formulas will catapult you to ‘Guru Status’ in your office and I’ll do my best to make this painless, but you might need to get a brainbooster (a fruit or veg snack given to the 5 year olds at my son’s school at 10am each morning when they’re starting to lose focus).

Array formulas are ideal for summing or counting cells based on multiple criteria, a bit like SUMIF and SUMIFS, and COUNTIF and COUNTIFS but better, especially if you only have Excel 2003 and don’t have the *IFS functions.

And unlike the SUMIFS and COUNTIFS functions which only allow you to specify AND criteria, with array formulas you can specify OR criteria too.

Using the data in the table as an example the
SUMIFS is limited to this type of criteria:

Sum Sales if the salesperson is John AND the region is North

SUMIFS Formula:

=SUMIFS(Sales,Sales_Person,”John”, Region,”North”)

=142

Whereas with an array formula you can do this:

Sum Sales if the salesperson is John OR Jim AND the region is North OR South

Array Formula:

{=SUM((Sales)*((Sales_Person=”John”)+(Sales_Person=”Jim”))*((Region=”North”)+(Region=”South”)))}

=306

We can see from the array formula example above that the * symbol instructs Excel to interpret the criteria as AND, and the + symbol is OR.

Note: the cell ranges in the formulas above have been replaced with named ranges to make it easier to read, quicker to build and easier to follow when you come back to the workbook months later.

Download the workbook and follow along or reverse engineer the formulas.

What is an Array?

An array is a series of data values, as opposed to a single value in one cell. Arrays can be contained in a single row, a column or multiple rows and columns. And array formulas can be entered in a single cell or a range of two or more cells. In this tutorial we’re going to look at single cell array formulas.

You will recognise an array formula in Excel because it is enclosed in curly brackets { } see example above. These curly brackets are inserted by pressing CTRL+SHIFT+ENTER upon entering the formula, and as a result array formulas are sometimes known as ‘CSE formulas’. 

Array Formula Example

Let’s take a look at what’s actually happening in Excel when we insert an array formula.

Excel array formulas are testing for TRUE or FALSE outcomes with a numerical equivalent for TRUE being 1, and for FALSE being 0. These TRUE or FALSE values are known as Boolean values.

Taking the above example Excel is finding whether the values in the salesperson column are either John or Jim, and awarding the cell a 1 or a 0 based on the outcome.

So the Sales Person values are {1,1,0,1,1,0}

It then does the same for the Region:

{1,1,1,1,1,1}

And then multiplies these outcomes by the Sales column and adds up the result for each row:

{1x1x76}+{1x1x94}+{0x1x54}+{1x1x66}+{1x1x70}+{0x1x80} = 306

How to Enter an Array Formula

As mentioned earlier, array formulas are surrounded with curly brackets. You must not type in these curly brackets (if you do your formula won’t work). They must be entered by pressing CTRL+SHIFT+ENTER when you enter the formula, as opposed to just pressing ENTER.

You will notice the curly brackets disappear when you edit the cell. When you’re finished editing you need to press CTRL+SHIFT+ENTER again to enter the formula correctly.

Note: While the SUMPRODUCT function is an array formula it doesn’t require curly brackets, or to be entered using CTRL+SHIFT+ENTER.

Array Formula Examples

Using the sample data on the right let’s work
through some scenarios.

Note: Again I’ve used named ranges for each column (Employee, Date and Overtime) as you will see in the formulas below.

COUNTIF Using an Array Formula and a Date Range

If you have Excel 2007 or Excel 2010 you can do these with the COUNTIFS function.

Scenario 1

Count the number of overtime days worked per person for each month

Array Formula for May in cell S6

{=SUM((Employee=$R6)*
(Date>=DATEVALUE(“1/05/2011″))*(Date<=DATEVALUE("31/05/2011")))}

Array Formula for June in cell T6

{=SUM((Employee=$R6)*
(Date>=DATEVALUE(“1/06/2011″))*(Date<=DATEVALUE("30/06/2011")))}

Result

Scenario 2

Count the number of overtime days worked per person each month that were more than 1 hour

Array Formula for May in cell S18

{=SUM((Employee=$R18)*(Date<=DATEVALUE("1/05/2011"))*(Date>=DATEVALUE(“31/05/2011″)*(Overtime>1)))}

Array Formula for June in cell T18

{=SUM((Employee=$R18)*(Date<=DATEVALUE("1/06/2011"))*(Date>=DATEVALUE(“30/06/2011″)*(Overtime>1)))}

Result

AVERAGE Using Array Formulas

Scenario:

Average the overtime hours worked per person each month

If you have Excel 2007 or Excel 2010 you can do this with the AVERAGEIFS function.

Array formula in cell P30:

{=AVERAGE(IF((Employee=$O31)*(Date>=DATEVALUE(“1/05/2011″))*(Date<=DATEVALUE(“31/05/2011″)),Overtime))}

Result

Note: When the IF function is used in an array formula it evaluates slightly differently. It creates an intermediate array using cell references for TRUE, and ‘FALSE’ values for FALSE.

For example an intermediate array might look like this:

{FALSE, FALSE, FALSE, FALSE, N10, N11}

Where N10 and N11 are TRUE i.e. they match the criteria. And since the average function in Excel is set to ignore Boolean values it will only average N10 and N11 ignoring the FALSE values thus creating a true AVERAGE.

Array Formula Rules

1.       The ranges referred to in your array formulas must be the same size, otherwise you will get an error. For example in the formula below you can see that although the formula may refer to different columns of data the length of the column is from row 6 to row 11 in each case.

{=SUM((C6:C11)*((A6:A11=”John”)+(A6:A11=”Jim”))*((B6:B11=”North”)+(B6:B11=”South”)))}

2.       To enter the array formula you must press CTRL+SHIFT+ENTER, that is hold down CTRL and SHIFT and then press ENTER releasing all of them together. If you use a Mac it’s COMMAND+RETURN.

3.       Do not enter the curly brackets yourself, Excel does it when you press CTRL+SHIFT+ENTER.

4.       When the multiplication symbol * is used it means AND, and when the plus symbol + is used it means OR.

Download the workbook and follow along or reverse engineer the formulas.