Excel - SUMIFS and OR

Asked By MSWesterbee on 22-Aug-07 10:24 AM
Hi,

How do i combine SUMIFS with the OR-function? I need to check a column
(turnovers) against multiple criteria (columns): date1, date2, status,
country, region.

As long as the criteria has one possible value, no problem (this is the case
with date1&2, status and country).

But, for the criteria "Region" i need to determine whether it meets multiple
criteria ("sum if" region=WE "OR" region=US "OR" region=RU).

I tried to include the OR-function (like i use the "greater then" symbol:
enourmous long formula.

Anybody any advice? Thanks in advance!!




Michae replied on 22-Aug-07 10:38 AM
The OR function has to go first for example:
=OR(SUMIF(A2:A5,"WE",B2:B5),SUMIF(A2:A5,"US",B2:B5))

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.
Michae replied on 22-Aug-07 10:52 AM
This only returns true or false, if you are trying to add them together use
this:
=IF(OR(SUMIF(A2:A5,"RU",B2:B5),SUMIF(A2:A5,"WE",B2:B5),SUMIF(A2:A5,"US",B2:B5),SUMIF(A2:A5,"PE",B2:B5)),SUM(B2:B5),"")
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.
Bob Phillips replied on 22-Aug-07 10:57 AM
=SUMPRODUCT(--(region={"WE","US","RU"}))

--
---
HTH

Bob

(there is no email, no snail mail, but somewhere should be gmail in my addy)
Peo Sjoblom replied on 22-Aug-07 10:57 AM
=SUM(SUMIF(A2:A100,{"WE","US","RU"},B2:B100))


adapt the ranges to fit accordingly



--
Regards,

Peo Sjoblom
Bob Phillips replied on 22-Aug-07 10:58 AM
You probably want an amount range as well

=SUMPRODUCT((region={"WE","US","RU"})*(amounts))

--
---
HTH

Bob

(there is no email, no snail mail, but somewhere should be gmail in my addy)
Harlan Grove replied on 23-Aug-07 02:24 PM
...

How could this have been useful to anyone except as an example of a
respondent who doesn't understand Excel formulas or can't comprehend OP's
questions?

OR(SUMIF(A2:A5,"RU",B2:B5),SUMIF(A2:A5,"WE",B2:B5),
SUMIF(A2:A5,"US",B2:B5),SUMIF(A2:A5,"PE",B2:B5))

is very close to a COMPLETELY MEANINGLESS expression. It'd only be false
when the sums corresponding to RU, WE ir US in col A are each zero, but col
B contains positive, negative or zero values, then there could be col A
entries that nevertheless sum to 0. Do you really believe the OP would want
to display "" in that case?