Sumproduct question

[bab01824]

I use sumproduct often in the form;

SUMPRODUCT((RESULT="Y")*(MODULE="Fax")*(BETA="Y))

, where RESULT, MODULE and BETA are column ranges and I am looking for a count where all three criteria are met.

But now I have to count incidences where the RESULT could be "Y" OR "B" in addition to the other criteria. Other than a long formula like;

SUMPRODUCT((RESULT="Y")*(MODULE="Fax")*(BETA="Y))+SUMPRODUCT((RESULT="B")*(MODULE="Fax")*(BETA="Y))

is there a simpler way?

bb

 

[Zack Barresse]

how about...

=SUMPRODUCT(or((RESULT="Y"),(RESULT="B"))*(MODULE="Fax")*(BETA="Y))

 

[Joe4]

Try this:

=SUMPRODUCT((RESULT={"Y","B"})*(MODULE="Fax")*(BETA="Y"))

 

[Aladin Akyurek]

Three options: In order of flexibilty and speed...

=SUMPRODUCT(--ISNUMBER(MATCH(RESULT,{"Y","B"},0)),--(MODULE="Fax"),--(BETA="Y"))

=SUMPRODUCT(((RESULT="Y")+(RESULT="B")),--(MODULE="Fax"),--(BETA="Y"))

=SUMPRODUCT((RESULT={"Y","B"})*(MODULE="Fax")*(BETA="Y"))

 

[Aladin Akyurek]

how about...

=SUMPRODUCT(or((RESULT="Y"),(RESULT="B"))*(MODULE="Fax")*(BETA="Y))

OR computes a scalar value, not an array, hence it won't work.

 

[Zack Barresse]

thank you Aladin,

i got to looking at that, and now see my folly. sorry bout the mis-post. fingers ahead of the mind

thank you for the correction!

i apologize bruce, didn't mean to screw up your post.

 

[bab01824]

I had tried the OR and it didn't work. The { } solution worked,

Thanks as always.

bb

 

[Aladin Akyurek]

... The { } solution worked...

That's not too specific, is it?