Consulting

Results 1 to 8 of 8

Thread: SUMPRODUCT Step By Step - Part 2

  1. #1
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,081
    Location

    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

    	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

    	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

    	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.
    Last edited by xld; 04-19-2009 at 01:41 PM.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  2. #2
    VBAX Expert TrippyTom's Avatar
    Joined
    Jul 2005
    Location
    New York, NY (USA)
    Posts
    556
    Location
    In my effort to understand this elusive function, I am reading this very carefully. But this part stumps me:
    =SUMPRODUCT((A1:A6="X")*(B20:B25>4))

    because the second range is smaller than the first.
    Aren't they the same? They're both 6 cells?
    Office 2010, Windows 7
    goal: to learn the most efficient way

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,081
    Location
    Thanks Tom, that is a typo, I will correct it.

    I hope it is helping, your feedback can help to improve future items.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    xld thank u
    it is really important subject

  5. #5
    VBAX Expert shrivallabha's Avatar
    Joined
    Jan 2010
    Location
    Mumbai
    Posts
    750
    Location
    Thank you Bob (If I may call you that)
    Regards,
    --------------------------------------------------------------------------------------------------------
    Shrivallabha
    --------------------------------------------------------------------------------------------------------
    Using Excel 2016 in Home / 2010 in Office
    --------------------------------------------------------------------------------------------------------

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,081
    Location
    Of course you may, it is my name.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    Fantastic, this helped me a alot after much searching.

  8. #8
    This line:
    "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"

    Is unclear when you reach the "However,". The previous line is in fact valid, but you were refering to the next example.

    If you could give it a new line it might help because I was confused until I read the next example.

    Also great guide so far. Onwards to Lesson3!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •