Monday Morning Mulling: August Challenge

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:

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:

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.

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:

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:

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:

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

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  

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:

i.e. the formula is now of the form  

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

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

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:

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.