SUMIFS and OR

[Guest]

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:
">"&). I would prefer not to use multiple SUMIFS, because then i get an
enourmous long formula.

Anybody any advice? Thanks in advance!!

 

[Guest]

The OR function has to go first for example:
=OR(SUMIF(A2:A5,"WE",B2:B5),SUMIF(A2:A5,"US",B2:B5))

 

[Guest]

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]

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

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Peo Sjoblom]

=SUM(SUMIF(A2:A100,{"WE","US","RU"},B2:B100))


adapt the ranges to fit accordingly

 

[Bob Phillips]

You probably want an amount range as well

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

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Harlan Grove]

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),"")

....

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?