View Full Version : [SOLVED:] 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?
Paul_Hossler
06-11-2012, 12:01 PM
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
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
06-11-2012, 05:05 PM
Any reason you couldn't use a User Defined Function written in VBA?
Paul
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
06-12-2012, 05:12 AM
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
06-13-2012, 02:50 AM
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))))
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
=SUMPRODUCT(--(MyContacts!$B$1:$B$40<>""),--(ISNA(MATCH(MyContacts!$B$1:$B$40,{"Teacher","NA",1967,1976},0))))
Amazing stuff! Thanks.
Powered by vBulletin® Version 4.2.5 Copyright © 2025 vBulletin Solutions Inc. All rights reserved.