View Full Version : 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 © 2020 vBulletin Solutions Inc. All rights reserved.