# More conditions for SUMPRODUCT formula

#### bbsaid99

##### New Member
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

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
bbsaid99 said:
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

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

Thanks so much.

1,078,430
Messages
5,340,204
Members
399,360
Latest member
HDavid21

### This Week's Hot Topics

• Problem with Radio Button's format control
I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
• Last Display on userform to a Listbox
[CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
• Rename and move files to a new location
Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
• Help with True/False Formula
Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
• Clear extra characters from a provided range of cells
Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
• Help with Current and highest streaks
Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...