Array formulas and sumproduct

Using array formulas rather than sumproduct

We looked at the use of sumproduct() previously. Lets repeat that using array formulas instead. Array formulas will give you more solution options than sumproduct() can - anyway you will see that sumproduct() is just a special case of an array formula sum() . In all these examples, except where noted, you do not need to enter the surrounding {}. Use control/shift/enter instead of enter when you are entering or amending the formula in the formula bar. If you get an unexpected result or and error entering an array formula, its usually because you forgot to do this. In this and subsequent pages, I will sometimes refer to array formulas as CSE (control/shift/enter) - you'll find this is commonly used on websites about excel.

Using Sum() 

You can do anything you would do with sumproduct() using a combination of sum(), sumif(), count(), countif() or if() entered as array formulas. The new functionality that came with Excel 2007, sumifs() will be covered elsewhere as it achieves the same thing without appearing to use arrays.

An example of where you could use it

Lets get back to our example we used to illustrate the use of sumproduct().I'll assume you have already created the appropriate named ranges and we are going to reuse them here. 

Your input data looked like this.



The named ranges we will use are MyExpensesItems, MyExpenses, MyExpensesDate, MyExpensesPaidCash according to the method in Named Ranges with Column Headings. If you don't want to bother creating these then just substitute them with $b$2:$b$6 etc in the following formulas as appropriate.

Breaking it down


We calculated this table



The sumproduct() version was 

=SUMPRODUCT(--(myExpensesItems=$H3)* --(MyExpensesPaidCash="Yes")* --(MyExpensesDate<DATE(YEAR(I$2),MONTH(I$2)+1,1))* --(MyExpensesDate>=DATE(YEAR(I$2),MONTH(I$2),1))* MyExpenses)

The same thing can be achieved using a CSE as follows - Enter the following as an array formula. --it's the same result! Sumproduct is really just a sum() array formula that you didnt have to enter as an array.

{=SUM(--(myExpensesItems=$H3)* --(MyExpensesPaidCash="Yes")* --(MyExpensesDate<DATE(YEAR(I$2),MONTH(I$2)+1,1))* --(MyExpensesDate>=DATE(YEAR(I$2),MONTH(I$2),1))* MyExpenses)}


If you understood how sumproduct worked, you also now understand how array formulas work - its that simple. Using arrays allows us to to give other functions same kind of power that sumproduct() gives to sum() 

Single value -vs- array results

In the introduction to array formulas we were looking at returning lists of values to populate cells. Generally speaking, as with the sum() example above we return a single result. That single result is generally the result of some summarizing operation on the the list of values in the array such as count, sum, average etc. 

Note we can use IF. This takes the average() of items where the description is "Sugar"
{=SUM(IF(myExpensesItems="Sugar",MyExpenses,0))/SUM(--(myExpensesItems="Sugar"))}

But wait - the above can be achieved using averageif(), or a combination of countif() and sumif() - so why bother with a CSE ?
=AVERAGEIF(myExpensesItems,"Sugar",MyExpenses)

Certainly this CSE gives the same result too.
{=AVERAGE(IF(myExpensesItems="Sugar",MyExpenses,FALSE))}

But what if you wanted to take the average of those items paid cash and sugar ? 
{=SUM(IF(--(myExpensesItems="Sugar")*--(MyExpensesPaidCash="Yes"),MyExpenses,0))/SUM(--(myExpensesItems="sugar")*--(MyExpensesPaidCash="Yes"))}
In this case, you have to use a CSE, so you can see that they open the door to ever more complex uses, but at the same time are very familiar. In the next section we are going to cover something less familiar.