Consulting

Results 1 to 8 of 8

Thread: And vs Or and Not Conditions

  1. #1
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location

    And vs Or and Not Conditions

    I have enjoyed XLD's sticky posts (parts 1-3) on the SUMPRODUCT function. This function has allowed me to streamline and shorten many of my complex formulas employed in my workbooks. I am interested in learning the OR and NOT conditions seem to be working so differently than the AND conditions. For example, I have a workbook which includes the following possible conditions in Column B:

    "Teacher"
    "NA"
    "Unknown"
    "" [blank]
    1967
    1968
    1969
    1970
    1971
    1972
    1973
    1974
    1975
    1976

    Why does this formula:

    =SUMPRODUCT(--(MyContacts!$B$1:$B$4000={1968,1969,1970,1971,1972,1973,1974,1975,"Unknown"}))
    produce the correct count, while this formula:

    =SUMPRODUCT(--(MyContacts!$B$1:$B$4000<>{"Teacher","","NA",1967,1976}))
    results in a count that is grossly distorted? What is it about the <> that causes the second formula to respond differently than the = in the first formula? Do <> conditions have to be tested individually?

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    I have enjoyed XLD's sticky posts (parts 1-3) on the SUMPRODUCT function.
    Me too.


    The way I look at array formulas, is like a VBA For/Next loop: (rightly or wrongly)

    =SUMPRODUCT(--(MyContacts!$B$1:$B$14={1968,1969,1970,1971,1972,1973,1974,1975,"Unknown"  }))
    For i = 1 to 14
        If Bi = 1968 then Add 1
    Next i
    answer so far = 1
    For i = 1 to 14
        If Bi = 1969 then Add 1
    answer = 2
    Next I
    etc.

    When done, the anwser is 9 (correct)

    Applying the same logic to the inverse case ...

    =SUMPRODUCT(--(MyContacts!$B$1:$B$14<>{"Teacher","","NA",1967,1976}))
    For i = 1 to 14
        If Bi <> 1968 then Add 1
    Next i
    answer so far = 13
    For i = 1 to 14
        If Bi = "" then Add 1
    answer = 26
    Next i
    etc.

    When done, the answer is 65 (not correct)

    Of the 14 values, 13 are not = each of the 5 conditions, so 5 x 13 = 65

    At least that's the way I look at it

    Maybe someone can come up with a clever formula so you won't need to test each case with a seperate SUMPRODUCT

    Paul

  3. #3
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    Quote Originally Posted by Paul_Hossler
    Me too.


    The way I look at array formulas, is like a VBA For/Next loop: (rightly or wrongly)

    ...

    At least that's the way I look at it

    Maybe someone can come up with a clever formula so you won't need to test each case with a seperate SUMPRODUCT

    Paul
    Thanks. That makes sense after walking through your examples. That seems to be precisely what is happening. Hopefully there is away to avoid having to test each <> condition separately.

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    Any reason you couldn't use a User Defined Function written in VBA?


    Paul

  5. #5
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    Quote Originally Posted by Paul_Hossler
    Any reason you couldn't use a User Defined Function written in VBA?

    Paul
    Actually, no, and I may end up doing that. I've just been experimenting with Sumproduct since I learned I can group criteria {cri1, cri2, cri3} when adding or counting with the = operator. My main objective here is just to explore the capabilities of Sumproduct. My curiosity got the best of me when I ran into the roadblock with the <> operator. It amazes me just how flexible and expandable Sumproduct is.
    Last edited by Opv; 06-11-2012 at 05:54 PM.

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    It amazes me just how flexible and expandable Sumproduct is.
    Yea, Bob's 3 stickies were an eye opener to some of the hidden power in Excel

    Paul

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    NOTs in SUMPRODUCT and other array formulas can be quite tricky.

    Paul is right, the <> version that you are using is comparing each of the constant values against each of the cell values, so many are being counted multiple tim4es.

    The = test you originally used was using a neat shorthand version, but the not equal version can still be handled by SUMPRODUCT, by using a more 'structured' approach, using MATCH.

    So here, we would test whether any of the constant values are in the range using

    MATCH(MyContacts!$B$1:$B$4000,{"Teacher","","NA",1967,1976},0)
    This returns the position of the cell value in the constants array, or #N/A if not found. So we turn this to TRUE or FALSE by using ISNA against it

    ISNA(MATCH(MyContacts!$B$1:$B$40,{"Teacher","","NA",1967,1976},0))
    Unfortunately, the blank test does not work as you have it, so you need to add another test for that
    --(MyContacts!$B$1:$B$40<>"")

    In full, to get your count

    =SUMPRODUCT(--(MyContacts!$B$1:$B$40<>""),--(ISNA(MATCH(MyContacts!$B$1:$B$40,{"Teacher","NA",1967,1976},0))))
    Last edited by Bob Phillips; 06-13-2012 at 01:12 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

  8. #8
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    Quote Originally Posted by xld
    NOTs in SUMPRODUCT and other array formulas can be quite tricky.

    Paul is right, the <> version that you are using is comparing each of the constant values against each of the cell values, so many are being counted multiple tim4es.
    Thanks. I see that now.

    Quote Originally Posted by xld
    In full, to get your count

    [vba]=SUMPRODUCT(--(MyContacts!$B$1:$B$40<>""),--(ISNA(MATCH(MyContacts!$B$1:$B$40,{"Teacher","NA",1967,1976},0))))[/vba]
    Amazing stuff! Thanks.

Posting Permissions

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