# Monday Morning Mulling: August Challenge

29 August 2016

*Final Friday Fix: August Challenge Recap*

On Friday, I asked you to consider the following report (you can download the Excel file example here):

The aim was to be able to cross-multiply all costs that meet certain criteria. The criteria (Business and Account Type) were provided in cells **C6:C9** and **D6:D9**, with the number of accounts in cells **E6:E9**. The hours required for the various processes per account type and business was in cells **F6:K9** with the hourly rates by process in cells **F11:K11**. Clear as mud?

In summary, if all costs were calculated, you’d need to cross multiply all of the cells, *e.g.* (117 x 3.4 x $15) + (117 x 3.0 x $24) + (117 x… *etc.*

But I made it harder than that. Firstly, I only wanted to include costs that met certain criteria (Business “B” and account type “Deluxe) and secondly, I decided to make one of the hourly rates “n/a”.

In one cell, without adding any helper rows or columns or using different data or VBA, I wanted you to calculate the total costs in one cell – and I even gave one solution, viz.

The problem with this solution is (a) it won’t scale (*e.g.* add extra accounts or processes) and (b) even Einstein would struggle to follow it (especially as he’s dead).

Here’s my solution:

**{=SUMPRODUCT(E6:E9*F6:K9*IF(NOT(ISNUMBER(F11:K11)),,F11:K11)*(C6:C9=E16)*(D6:D9=E17))}**

Simple!

If that’s all you need to know, glad to be of help. For us mere mortals, let me explain below in a bit of prose that Tolstoy would no doubt be proud of.

Let me introduce our namesake, the humble **SUMPRODUCT** function. At first glance, **SUMPRODUCT(vector1,vector2,...)** appears quite ordinary. However, before showing an example, though, look at the syntax carefully:

- A
**vector**for Excel purposes is a collection of cells either one column wide or one row deep. For example,**A1:A5**is a column vector,**A1:E1**is a row vector, cell**A1**is a unit vector and the range**A1:E5**is not a vector (it is actually an array, but more on that later). The ranges must be contiguous;*and* - This basis functionality uses the comma delimiter (
**,**) to separate the arguments (vectors). Unlike most Excel functions, it is possible to use other delimiters, but this will be revisited shortly below.

I have an example workbook you can play with here. From it, consider the following sales report:

The sales in column H are simply the product of columns **F** and **G**, *e.g.* the formula in cell **H12** is simply **=F12*G12**. Then, to calculate the entire amount cell **H23** sums column **H**. This could all be performed much quicker using the following formula:

**=SUMPRODUCT(F12:F21,G12:G21)**

*i.e.* **SUMPRODUCT** does exactly what it says on the tin: it sums the individual products.

Where **SUMPRODUCT** comes into its own is when dealing with multiple criteria. This is done by considering the properties of TRUE and FALSE in Excel, namely:

- TRUE*number = number (
*e.g.*TRUE*7 = 7);*and* - FALSE*number = 0 (
*e.g.*FALSE*7=0).

Consider the following example:

we can test columns **F** and **G** to check whether they equal our required values. **SUMPRODUCT** could be used as follows to sum only sales made by Business Unit 1 for Product Z, *viz.*

**=SUMPRODUCT((F12:F21=1)*(G12:G21=”Z”)*H12:H21)**.

For the purposes of this calculation, (**F12:F21**=1) replaces the contents of cells **F12:F21** with either TRUE or FALSE depending on whether the value contained in each cell equals 1 or not. The brackets are required to force Excel to compute this first before cross-multiplying.

Similarly, (**G12:G21=”Z”**) replaces the contents of cells **G12:G21** with either TRUE or FALSE depending on whether the value “Z” is contained in each cell.

Therefore, the only time cells **H12:H21** will be summed is when the corresponding cell in the arrays **F12:F21** and **G12:G21** are both TRUE, then you will get TRUE*TRUE*number, which equals the said number.

Notice that **SUMPRODUCT** is not an array formula (*i.e.* you do not use **CTRL+SHIFT+ENTER**, please see here for more information but it is what is known as a “pseudo-array” function, so again it can use a lot of memory making the calculation speed of the file slow down.

Note also that this uses the ***** delimiter rather than the comma, analogous to TRUE*number, *etc*. If you were to use the comma delimiter instead, the syntax would have to be modified thus:

**=SUMPRODUCT(--(F12:F21=1),--(G12:G21=”Z”),H12:H21)**.

Minus minus? The first negation in front of the brackets converts the array of TRUEs and FALSEs to numbers, albeit substituting -1 for TRUE and 0 for FALSE. The second minus sign negates these numbers so that TRUE is effectively 1, rather than -1, whilst FALSE remains equals to zero. This variant often confuses end users which is why I recommend the first version described above.

** **

You can get more and more sophisticated:

So far, I have only considered **SUMPRODUCT** with vector ranges. Using the multiplication delimiter (*****), it is possible to use **SUMPRODUCT** with arrays (an array is a range of cells consisting of both more than one row and more than one column).

In the above example, **SUMPRODUCT** has been used in its elementary form in cells **I36:N36**. For example, the formula in cell **I36** is:

**=SUMPRODUCT($H$32:$H$35,I$32:I$35)**

and this has then been copied across to the rest of the cells.

To calculate the total costs of this retail bank example, this could be calculated as:

**=SUMPRODUCT($I$36:$N$36,$I$21:$N$21)**

However, the formula in cell **I41** appears more – and unnecessarily – complicated:

**=SUMPRODUCT($H$32:$H$35*$I$32:$N$35*$I$21:$N$21)**

The use of the multiplication delimiter is deliberate (the formula will not work if the delimiters were to become commas instead). It should be noted that this last formula is essentially

**=SUMPRODUCT(Column_Vector*Array*Row_Vector)**

where the number of rows in the **Column_Vector** must equal the number of rows in the **Array**, and also the number of columns in the **Array** must equal the number of columns in the **Row_Vector**.

The reason for this extended version of the formula is in order to divide the costs between Budget and Standard costs in my example. For example, the formula in cell J41 becomes:

**=SUMPRODUCT($H$32:$H$35*$I$32:$N$35*$I$21:$N$21*($G$32:$G$35=J$40))**

*i.e.* the formula is now of the form

**=SUMPRODUCT(Column_Vector*Array*Row_Vector*Condition)**

where **Condition** uses similar logic to the TRUE / FALSE examples detailed earlier. This is a powerful concept – and now you can see how this will answer our question.

Using the above syntax, I can try

Unfortunately, using the syntax described above, I obtain

**=SUMPRODUCT(E6:E9*F6:K9*F11:K11*(C6:C9=E16)*(D6:D9=E17))**

But that gives me *#VALUE! *That’s because cell **H11** contains the text “n/a”. **SUMPRODUCT **doesn’t like text (that’s the point of our challenge!). **SUMIFS **does. For example:

Do you see **SUMIFS **works even though I have failed to explain either the syntax or the formula? (This art That’s because **SUMIFS** only works on vectors and my challenge – deliberately – uses arrays. My solution does employ **SUMPRODUCT** but in an unusual way – and the original example file gave you a massive clue. Did you spot it?

Did you notice “n/a” was highlighted bold italic yellow on red in the original Excel file?

That’s because I used conditional formatting (click here to find out more about conditional formatting). It highlights the cell accordingly if

**=NOT(ISNUMBER(F$11))**

is TRUE. **ISNUMBER** determines if a cell or value is a number; **NOT** converts TRUE to FALSE and *vice versa*. Therefore, a cell is highlighted if it does not contain a number.

Now consider my solution. It expands on my first attempt for the solution as follows:

**{=SUMPRODUCT(E6:E9*F6:K9*IF(NOT(ISNUMBER(F11:K11)),,F11:K11)*(C6:C9=E16)*(D6:D9=E17))}**

Note that this formula replaces **F11:K11** with **IF(NOT(ISNUMBER(F11:K11)),,F11:K11)**. That means text is treated as zero – a nice workaround trick which makes the **SUMPRODUCT** syntax work provided that the formula has been entered as an array (**CTRL + SHIFT + ENTER** to get the ‘curly brackets’ formally known as braces). (This is a syntax requirement.)

No doubt I will be avalanched with simpler solutions – but hey, it works and it is *a* solution!

For more on **SUMPRODUCT** and working with multiple criteria, check out many examples at www.sumproduct.com/thought.

*The Final Friday Fix will return on Friday 30 September with a new Excel Challenge. In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every other business workday.*