And vs Or and Not Conditions

[Opv]

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?

[Paul_Hossler]

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

[Opv]

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.

[Paul_Hossler]

Any reason you couldn't use a User Defined Function written in VBA?


Paul

[Opv]

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.

[Paul_Hossler]

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

[Bob Phillips]

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))))

[Opv]

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.

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.