SumIfs(or)?

[Reddog94]

Can an OR function be imbedded in a SumIfs function? For example, I want to do a SumIfs with two criteria, however the second criteria could be 3 different values.

For example,

Column 1 = department number
Column 2 = expense category
Column 3 = data to be summed

I want to sum all data in column 3 if column 1 = "1234" and column 2 = either "labor", "fringe", or "merit".

Using Excel 2007.
Thank you.

 

[T. Valko]

=SUMPRODUCT(--ISNUMBER(SEARCH(" "&A1&" "," "&C1:C9&" ")),D1:D9)

" "&A1&" "
How is the criteria above picking up "ship x" "x ship" and " ship "? The way it's written seems like only " ship " would calculate.

" "&C1:C9&" "
Why are the space appendings necessary for the search range?

Why is -- necessary?

Exactly what I need. Thank you Biff!

We concatenate the spaces so that we search for " ship ". This way we eliminate all of the other derivatives from being counted as "false positives".

See this for more info on the double unary minus --:

http://xldynamic.com/source/xld.SUMPRODUCT.html