Can SUMIFS use the OR function?
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. |
Can SUMIFS use the OR function?
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. |
Can SUMIFS use the OR function?
Hi,
You will be better off with the Sumproduct() function ... HTH |
Can SUMIFS use the OR function?
=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. |
Can SUMIFS use the OR function?
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. |
Can SUMIFS use the OR function?
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. |
Can SUMIFS use the OR function?
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. |
Can SUMIFS use the OR function?
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. |
All times are GMT +1. The time now is 03:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com