How can I use OR function inside SUMIFS

[Baybora]

I asked thsi question last week and thanks Luke M for his kind interest.

However it did not solve my problem
for example I'm writing
SUMIFS(long and 4-5 ciriterias).
But in one cirteria I need to say "OR"
A long way is I write
SUMIFS(...................................)+SUMIFS(.....................).
I cant imagine if my need will be 3-4 ORs : )
Best wishes,
Baybora

 

[Eduardo]

Hi,
Please post your formula and indicate where you need the or, thanks
However you will need to use an if statement

=if(or(A1=0,B1=3),sumifs(...............

 

[Luke M]

Hello again!

Another way to get an OR type function with summation, is to multiply your
values against some "criteria arrays". When you do this, make sure you're
crtieria are not over lapping (eg x>5, x>10)

Let's say you want to sum column D when column A = "Tom" or column B = "Bob"
and column C = "Smith"

=SUMPRODUCT(--(((A:A="Tom")+(B:B="Bob"))>0),(C:C="Smith"),(D))

Note how the "or" criteria are added together, while the additional criteria
is seperated by a column. The ">0" check is in there for the cases when both
A and B equal their criteria.

For other readers, note that SUMPRODUCT can only callout entire columns
(A:A) when using XL 2007.

 

[Baybora]

Hallo Master Luke M,

I had started with sumproduct but again I could not pass this OR mountain
pass. Then I had deceided to use SUMIF solution and met the same rock again.
This magic >0 solved my problem.
Really thank you.

And also thanks Eduardo for kind his interest.

Best wishes,

Baybora