Can SUMIFS use the OR function?

[Ted M H]

I have a table with columns Product, Units Sold, Sales manager.
Products values are A, B, C, D, E, F
Units sold is a simple integer
Sales manager values are Smith, Jones, Doe
Can I use SUMIFS to return the total units sold if manager name is Smith AND
Product is A, OR manager name is Smith and Product is B.

I tried to use OR in the second criteria argument, but can’t get it to work.
I know there are other ways to solve this problem, but I’m trying
specifically to understand what SUMIFS can and can’t do.
Thanks.

 

[ryguy7272]

I believe this will give you what you want. It may be a little dense, just
read through, slowly and thoroughly, and try a few examples on your own
scenario.

Regards,
Ryan--

 

[Carim]

Hi,

You will be better off with the Sumproduct() function ...

HTH

 

[Teethless mama]

=SUM(SUMIFS(Unit_Sold,Product,{"A","B"},Sales_manager,"Smith"))

 

[Ted M H]

Hi Ryan,

I may be a little dense, but I don't see anything in your reply that I can
read through other than my own post...Did you insert something here that I
can't see?

Thanks for the reply.

 

[ryguy7272]

Yeah, sorry, forgot to paste the link in before hitting the 'Post' button:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Regards,
Ryan--

 

[Ted M H]

Hi Everyone,

Thanks for taking the time to reply to my post. I'm going to guess that
since all three of you answered by pointing me toward SUMPRODUCT rather than
using OR with SUMIFS that in fact one cannot use OR with SUMIFS.

Thanks.

 

[T. Valko]

See teethless' post.

Technically, that formula is not calculating an "OR" condition. It's doing 2
independent SUMIFS and then adding those 2 results together. So, it's a
semantic "OR" condition!

It's similar to this:

A1:A5 = x, x, y, x, y

=SUM(COUNTIF(A1:A5,{"x","y"}))

In essence, this is what is happening:

=SUM(COUNTIF(A1:A5,"x"),COUNTIF(A1:A5,"y"))

=SUM({3,2})