Finding Your Perfect Match

Here, we consider how to undertake a case sensitive match. By Liam Bastick, Director with SumProduct Pty Ltd.

Query

I am trying to sum data based on case sensitive criterion and cannot get Excel to distinguish between upper and lower case characters, which is resulting in incorrect results. Any suggestions?

Advice

This is quite a common question over the years causing problems for many. To illustrate the issue, consider the following example which comes from the attached Excel file:

Various text strings in cells F15:F24 have been allocated corresponding values in cells G15:G24. The numbers have been chosen deliberately so that it is clear which rows have been included in the overall total.

We have discussed working with criteria before (see Dealing with Multiple Criteria and SUMPRODUCT Squared..?). Unfortunately, it is clear to the eye that only row 19 contains “sumproduct” and so the results for looking up “sumproduct” in the table should be just 16 and not 116 as returned presently.

To circumvent these problems, we consider a combination of two functions.

Remembering an old friend: SUMPRODUCT

Imagine we are presented with 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.

Dealing with Multiple Criteria

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.

Meet a new friend: introducing the EXACT function

EXACT is a function we haven’t discussed previously. Its syntax is as follows:

This function compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. It should be noted that EXACT is case-sensitive but ignores formatting differences.

Using our original example, EXACT is very simple to understand: either something is precisely the same or it is not.

Having brought these two functions into sharp focus, the suggested answer should be imminently obvious.

Suggested solution

The suggested solution to our problem is just a combination of these two functions, viz.

The suggested solution is

which simply includes only values that precisely match the value typed in cell F40. The attached Excel file demonstrates a similar solution.