Multiple Criteria with OR: a True SIGN of the Times…

In this article, we revisit (yet again!) one of the most queried areas of modelling: how to sum data based upon multiple criteria. The twist this time: what if only one of the various criteria specified has to be true? By Liam Bastick, director with SumProduct Pty Ltd.

Query

I have looked through a previous article on working with Multiple Criteria (see Dealing with Multiple Criteria for further information), but this addresses how to sum data when all criteria specified are true. What if I require at least one criterion to hold instead?

Advice

I am seriously thinking of writing a book on dealing with multiple criteria, such is the volume of queries on the subject! This time, I am considering an OR situation, i.e. I don’t need all criteria specified to hold.

Excel has two functions that deal with OR:

1. OR(Condition_1,Condition_2,…) checks to see if any of the logical conditions specified are TRUE. As long as at least one condition is TRUE, OR will be TRUE also;
2. XOR(Condition_1,Condition_2,…) checks to see if each of the logical conditions specified are TRUE. As long as at one and only one condition is TRUE, XOR will be TRUE also. This is often referred to as “exclusive or”.

XOR is a new function in Excel 2013 and therefore is not backwards compatible with earlier versions. In any case, OR and XOR do not work well with array formulae or functions that behave like arrays (“pseudo-array functions”), such as SUMPRODUCT.

Oh yes, good ol’ SUMPRODUCT once more…

A Recap on SUMPRODUCT

At first glance, SUMPRODUCT(vector1,vector2,…) appears quite humble. 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.

For more on SumProduct, please see SumProduct Squared..?

Dealing with Multiple Criteria where ALL must be TRUE, using SUMPRODUCT

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 Array of Light), but it is an array function, so again it can use a lot of memory making the calculation speed of the file slow down.

But what if we only need one of these two criteria to be TRUE..?

Illustrative Scenario

Let us imagine we run a car sales company with four divisions: North, South, East and West. Further, we only sell two types of car: the Mercudi and the Lexota. The attached Excel file can be used to follow this illustration:

You are the General Manager responsible for the North Division and Mercudi sales. Each month you have to provide a report summarising the total sales you are responsible for. This requires analysis of multiple criteria, but it is an OR, rather than an AND, situation.

We need to include sales of North Division and sales of Mercudi. However, if we do it this simply sales of Mercudi made by the North Division will be double counted:

If we specify the criteria in the spreadsheet as follows:

The formula in this instance would be:

However, there’s a simpler approach…

Introducing SIGN

Not many know this obscure but useful little Excel function. SIGN(Number) is:

• 1 if Number is positive;
• 0 if Number is zero; and
• -1 if Number is negative.

It’s only when you start combining this function with SUMPRODUCT do you realise how useful it can be. For example, in our scenario above, consider the following formula:

Inside the nested SIGN function, there are two criteria:

• Whether the Division is North (F12:F29=G34); and
• Whether the car sold is the Mercudi (G12:G29=G35).

Each criterion will either be TRUE (1) or FALSE (0), so the possible values inside the SIGN function are zero (neither criteria satisfied), one (only one criterion satisfied) or two (both criteria satisfied). If neither criteria is true, SIGN will return a value of zero; if one or more criteria is true, SIGN will return a value of one and hence sum the relevant values in column H. This is precisely what is required.

With more criteria considered, the simplicity of SUMPRODUCT(SIGN) becomes even more pronounced.

Extending the Idea

The attached Excel file considers three and four criteria, as well as two. For the sake of brevity here, I will jump straight to the four criteria scenario:

In this example, having been a very successful General Manager, you have acquired greater responsibility: not only do you remain responsible for the North Division and Mercudi sales, but you are now mentor to salesperson Alice and for trying to push credit (finance) sales.

As before, each month you have to provide a report summarising the total sales you are responsible for, which now considers four criteria: division, car, salesperson and finance:

If we specify the criteria in the spreadsheet as follows:

The “long” formula in this instance which would ensure the overlaps are not counted more than once would be:

It’s just so pretty. PhD’s are available for all those of you who can follow this formula in a heartbeat. Why on earth would you use the SUMPRODUCT(SIGN) variant (below) instead?

Erm, maybe because it’s shorter, easier to read, less memory intensive, takes less time to calculate, is less prone to reference errors, has fewer opportunities for logic errors,…

Until next time.