SUMIFS and OR
 

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!!

SUMIFS and OR
 

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.




"M.S. Westerbeek" wrote:

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!!

SUMIFS and OR
 

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:B 5)),SUM(B2:B5),"")
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Michael" wrote:

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.




"M.S. Westerbeek" wrote:

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!!

SUMIFS and OR
 

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

--
---
HTH

Bob

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



"M.S. Westerbeek" wrote in message
...
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!!

SUMIFS and OR
 

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


adapt the ranges to fit accordingly



--
Regards,

Peo Sjoblom



"M.S. Westerbeek" wrote in message
...
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!!

SUMIFS and OR
 

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)



"M.S. Westerbeek" wrote in message
...
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!!

SUMIFS and OR
 

"Michael" wrote...
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",B 2: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.
....

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?