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 cant get it to work.
I know there are other ways to solve this problem, but Im trying
specifically to understand what SUMIFS can and cant do.
Thanks.

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

--
RyGuy


"Ted M H" wrote:

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 cant get it to work.
I know there are other ways to solve this problem, but Im trying
specifically to understand what SUMIFS can and cant do.
Thanks.

Hi,

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

HTH

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


"Ted M H" wrote:

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 cant get it to work.
I know there are other ways to solve this problem, but Im trying
specifically to understand what SUMIFS can and cant do.
Thanks.

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" wrote:

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

--
RyGuy


"Ted M H" wrote:

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 cant get it to work.
I know there are other ways to solve this problem, but Im trying
specifically to understand what SUMIFS can and cant do.
Thanks.

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

Regards,
Ryan--


--
RyGuy


"Teethless mama" wrote:

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


"Ted M H" wrote:

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 cant get it to work.
I know there are other ways to solve this problem, but Im trying
specifically to understand what SUMIFS can and cant do.
Thanks.

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.

"Ted M H" wrote:

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 cant get it to work.
I know there are other ways to solve this problem, but Im trying
specifically to understand what SUMIFS can and cant do.
Thanks.

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})

--
Biff
Microsoft Excel MVP


"Ted M H" wrote in message
...
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.

"Ted M H" wrote:

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.