More conditions for SUMPRODUCT formula

[bbsaid99]

I have a SUMPRODUCT formula that counts the number of "Yes" in column B if a "1" appears in column A.

What I need it to do is count the number of yes answers in column B if there is a "1" OR "2" in column A.

Any help would be greatly appreciated.

Here is the formula I'm currently using:

=SUMPRODUCT(--(RawData!$A$1:$A$1000="1"),--(RawData!$B$1:$B$1000="Yes"))

 

[Domenic]

Assuming that the numbers in Column A are formatted as text, try...

=SUMPRODUCT(--(ISNUMBER(MATCH(RawData!$A$1:$A$1000,{"1","2"},0))),--(RawData!$B$1:$B$1000="Yes"))

Hope this helps!

 

[bbsaid99]

Thanks Domenic, but I can't seem to get the formula to work. I tried formatting the column as text, and had no results.

 

[Domenic]

Sorry bbsaid99,

Because you said you were currently using that particular formula, I assumed it worked and that your numbers in Column A were formatted as text. Since that doesn't appear to be the case, remove the quotes from the numbers within the array constant. The formula should be...

=SUMPRODUCT(--(ISNUMBER(MATCH(RawData!$A$1:$A$1000,{1,2},0))),--(RawData!$B$1:$B$1000="Yes"))

Hope this helps!

 

[bbsaid99]

Wow, thanks that worked like a charm.

To make it more complicated, is there any way I could add one more condition? Lets say it has to have 1 or 2 in column A, "Paper Clips" in column B and then count "Yes" in column C

I tried doing this, but it didn't work

=SUMPRODUCT(--(ISNUMBER(MATCH(RawData!$A$1:$A$1000,{1,2},0))),--(RawData!$B$1:$B$1000="Paper Clips"),--(RawData!$C$1:$C$1000="Yes"))

Thanks so much for your help

 

[Domenic]

I tried doing this, but it didn't work

=SUMPRODUCT(--(ISNUMBER(MATCH(RawData!$A$1:$A$1000,{1,2},0))),--(RawData!$B$1:$B$1000="Paper Clips"),--(RawData!$C$1:$C$1000="Yes"))

The formula is definitely correct. Make sure that the cells in Column B that contain "Paper Clips" don't have one or more extra spaces. The values need to match the criteria exactly.

 

[bbsaid99]

I must have made some mistake in my formula because I typed it in again and it worked.

Thanks so much.