Thread: SUMIF with an OR argument

1. Brain dead today. Column A1:A10 contains numbers. Column B1:B10 contains text (one word per cell). On a different worksheet, I have the words "red", "blue" and "green" in A1:A3. Column A1:A10 on worksheet 2 contains words as well, including the three listed. What I would like to do is in column B on worksheet 2 sum the numbers in column A on worksheet 1 but only where the words red, blue and green are present on worksheet 2. Any ideas? Thaks in advance. I tried SUMIF(Worksheet1 B1:B10,OR("red","blue","green"),Worksheet1 A1:A10) but it didn't do it!

2. Can you try again? I'm getting confused over what is where - A1:A10 seems to contain numbers in one sentence, and words in the next...

3. It would help if you posted a small sample workbook that gives us a clear and unambiguous idea of what you have and want.

4. Here you go.

5. Originally Posted by jlkirk
Here you go.
Never mind, figured it out: SUM(SUMIF(Sheet1!B1:B10,{"red","blue","green"},She et1!A1:A10))

6. Originally Posted by jlkirk
Never mind, figured it out: SUM(SUMIF(Sheet1!B1:B10,{"red","blue","green"},She et1!A1:A10))
Or as an array formula (confirm with Ctrl+Shift+Enter):

=SUM(IF(Sheet1!B1:B10={"red","blue","green"},Sheet 1!A1:A10))

7. Or this non-array formula :

=SUMPRODUCT((Sheet1!B1:B10={"red","blue","green"}) *Sheet1!A1:A10)

Regards
Bosco

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•