1 Attachment(s)
SUMPRODUCT Step By Step - Part 2
Multiple Conditional Tests
Overview
In the previous chapter we explored the basics of SUMPRODUCT, its basic form where it is used to multiply two or more arrays and sum the results, and then looked at how conditional tests could be used within SUMPRODUCT to count those conditional matches. Whilst this was interesting and (hopefully) informative, as this only achieving what can be done with COUNTIF it is not yet the full story.
Testing Two Conditions
Suppose that we want to test two conditions in a single formula. For example, with the following data
Code:
A
1 11
2 2
3 8
4 5
5 53
6 9
and we want to count how many items that are greater than 4 but less than or equal to 20. As you can see, we have 4 that meet these criteria, one is less than 4, one is greater than 20.
Logically, the two tests would be
(A1:A6>4) AND (A1:A6<=20)
which unfortunately does not work within SUMPRODUCT, that is if you try
=SUMPRODUCT((A1:A6>4)AND(A1:A6<=20))
Excel will complain, and will seek to change this to
=SUMPRODUCT((A1:A6>4)*AND(A1:A6<=20))
which, whilst it returns a result of 0 that is wrong, it does give us a clue.
Interestingly, if there were no numbers in that range that were less than equal to 4 and none greater than 20 it would return the correct result … but for the wrong reasons (we will not go into the details of this yet).
If we remove the AND, giving
=SUMPRODUCT((A1:A6>4)*(A1:A6<=20))
we get the correct result, 4.
What is happening here is that the first conditional test of (A1:A6>4) returns an array of
{TRUE;FALSE;TRUE;TRUE;TRUE;TRUE},
the second condition of (A1:A6<=20) returns an array of
{TRUE;TRUE;TRUE;TRUE;FALSE;TRUE},
and the operator between these conditions looks suspiciously like the Excel multiply symbol, which of course it is. As we discussed earlier, multiply TRUE by TRUE gives 1, any other combination of Booleans gives 0, so (A1:A6>4)*(A1:A6<=20) is equivalent to
{TRUE;FALSE;TRUE;TRUE;TRUE;TRUE}*{TRUE;TRUE;TRUE;TRUE;FALSE;TRUE},
which returns an array of {1;0;1;1;0;1}. You should be able to see that this is easily summed by SUMPRODUCT, giving a result of 4 as required.
Better Than Countif?
So, we can see that SUMPRODUCT can handle two conditions, but is this anything radically new? In this case, we could get the result by using COUNTIF. Not a single COUNTIF, but by counting how many items are greater than 4 and then counting how many items are greater than 20, subtracting one from the other, we get the same result. In other words
=COUNTIF(A1:A6,">4")-COUNTIF(A1:A6,">20")
Gives a result of 4 as there are 5 items greater than 5, one greater than 20, so subtracting one from the other gives 4.
So SUMPRODUCT is no better than COUNTIF then? Not in this example perhaps, but what if we wanted 3 conditions, 4, 5 and so on, the COUNTIF gets somewhat cumbersome, SUMPRODUCT stays reasonably concise.
More importantly, consider the following data
Code:
A B
1 X 4
2 X 5
3 Y 6
4 Y 4
5 X 1
6 X 5
where we want to count how many times column A shows an X where its corresponding value in column B is greater than 4. By sight, we can see there are two such instances, which is easily calculated with the following SUMPRODUCT formula
=SUMPRODUCT((A1:A6="X")*(B1:B6>4))
Try doing that with COUNTIF!
One little aside here, not that in COUNTIF to test for greater than 4, you have to enclose the test within quotes, “>4”, but in SUMPRODUCT it is a straight comparative test. A small point, but much cleaner looking in my view.
More Than 2 Conditions
This technique can easily be extended to more than 2 conditions as suggested above. Consider the above data extended like this
Code:
A B C
1 X 4 M
2 X 5 F
3 Y 6 M
4 Y 4 F
5 X 1 F
6 X 5 M
Where we want to count how many times column A shows an X where its corresponding value in column B is greater than 4 and its corresponding value in column C is M. Three conditions which should produce a result of 1. This SUMPRODUCT formula
=SUMPRODUCT((A1:A6="X")*(B1:B6>4)*(C1:C6="M"))
gives the required result. It should not be too difficult to see that this can be extended to 4, 5 or more tests if required.
Ranges To Test
In SUMPRODUCT, the ranges being tested MUST be the same size in order to work correctly. They do not have to be mapped too the same rows or columns, but they must be the same size.
This formula is valid
=SUMPRODUCT((A1:A6="X")*(B2:B7>4))
although it must be understood that this is comparing when a value in A1 equals X and the value in B2 is greater than 4. However, this formula is not valid
=SUMPRODUCT((A1:A6="X")*(B20:B23>4))
because the second range is smaller than the first.
In addition, whilst SUMPRODUCT is not array entered, it is an array function in this conditional usage, which means that you cannot use whole columns in the ranges to be tested, you have to use an explicitly limited range. This is where COUNTIF and SUMIF are preferable.
Note that this has changed in Excel 2007, which will be covered in more detail in a future part of these discussions.
Summary
In this section, we have extended SUMPRODUCT to count items that match multiple conditions, where the conditions are applied to a single range and then to conditions applied to separate ranges.
We have also shown how to extend the conditions tested beyond just two, and some of the rules to be adhered to when using SUMPRODUCT.
Next Time
We will extend into summing multiple conditions; using different operators; and OR conditions.
