How do I use "OR" inside a SUMIF statement

Question

Level 1

6 points

How do I use "OR" inside a SUMIF statement

Hello esteemed community,

I have searched for the asnwer to this question but the only thread I could find on it was dated 2008 and at the time the answer was "Not allowed". I am hoping this has since changed so I will ask again.

This is a classic case of trying to sum up various item that belong to one category in an expenses spreadsheet. So if I want all the expenses related to food, I would do:

SUMIF(C,"*FOOD*",E)

Now since I am copying these expenses from my bank account and they are labeled by store instead of by category, I would like a statement that says "Give me the sum of all expenses in column E when the description in column C includes "Safeway" OR "Ralphs" OR "Trader Joe's".

What would be the syntax for that?

Thank you very much.

Bo

iMac, OS X Mavericks (10.9.5)

Posted on Apr 16, 2015 8:10 AM

Reply

Answer 1

Apr 16, 2015 8:56 AM in response to Bmachine

SUMIF(C,"*Safeway*",E)+SUMIF(C,"*Ralphs*",E)

If you mean the text in the cell contains the term "Safeway" to "Ralphs" then there is a slightly, but tractable, harder solution

Reply

Answer 2

Bmachine Author

Level 1

6 points

Apr 16, 2015 7:20 PM in response to Wayne Contello

Thank you Wayne. Yes, repeating the statement is what I am doing now. I was hoping for a more compact solution like

SUMIF(C,"*Safeway*"OR"*Ralphs*"OR"*Trader Joe's*",E)

Thank you.

Reply

Answer 3

Apr 17, 2015 5:15 AM in response to Bmachine

an alternate way is to add another column with the formula (let's say in column F):

=OR(C="Ralphs", C="Trade Joe's")

then use the function:

=sumif(F, TRUE, E)

this will sum the values in column E when the value in the same row of column F is true. the values in column F are true when the values in row C are "Ralphs" or "Trader Joe's"

Reply