More conditions for SUMPRODUCT formula

bbsaid99

New Member
Joined
Dec 16, 2004
Messages
10
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
Joined
Mar 10, 2004
Messages
18,941
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
Joined
Dec 16, 2004
Messages
10
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
Joined
Mar 10, 2004
Messages
18,941
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
Joined
Dec 16, 2004
Messages
10
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
Joined
Mar 10, 2004
Messages
18,941
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
Joined
Dec 16, 2004
Messages
10
I must have made some mistake in my formula because I typed it in again and it worked.

Thanks so much.
 

Forum statistics

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

Some videos you may like

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...
Top