I want to do SUMIFs for the following image but for more than one flavor criterion. The two flavors highlighted in yellow both have the same date and location, so I'd like to be able to get the total for them both without just simply typing another +SUMIFS formula for the second flavor. Mainly because the list of flavors can be very long, so therefore the formula would become
too long. I've toyed around with some SUMPRODUCT formulas but can't get one to sum multiple flavors with multiple qualifiers.
https://drive.google.com/open?id=13yEfHm5cbggEuCAjffk4UjVm4tonCB-4
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Can someone help me with this? Probably a beginner level formula.
Excel Facts
Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
For example, this post here, I was hoping instead of SUMIFS(D:D,B:B,F5,C:C,G5,A:A,H5), it would be possible to just do: SUMIFS(D:D,B:B,F5,C:C,G5,A:A,H5:H8) and get it to apply for more than just 1 flavor.
Upvote
0
Sorry, not sure if I did or said something wrong. I'm not against pivot tables, I just was trying to give more information to be sure it will be the correct solution for me here since I don't know much about them and wasn't sure I gave enough detail for what I was trying to do.I give up
Have a nice day
Upvote
0
FormR
MrExcel MVP
- Joined
- Aug 18, 2011
- Messages
- 7,283
- Office Version
- 365
- Platform
- Windows
Hi, you can do it like this.
| Excel 2013/2016 | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | |||||||||||
| 2 | |||||||||||
| 3 | Flavor | Date | Location | Total | |||||||
| 4 | Oreos | 08/01/2019 | USA | 1000 | Date | Location | Flavor | Formula | |||
| 5 | Vanilla | 08/02/2019 | USA | 1000 | 08/01/2019 | USA | Oreos | 3500 | |||
| 6 | Chocolate | 08/01/2019 | USA | 1000 | Vanilla | ||||||
| 7 | Strawberry | 08/01/2019 | USA | 4000 | Chocolate | ||||||
| 8 | Double choc | 08/04/2019 | USA | 1500 | Cream | ||||||
| 9 | Pistachio | 08/05/2019 | USA | 1500 | |||||||
| 10 | Cream | 08/01/2019 | USA | 1500 | |||||||
| 11 | Raspberry | 08/06/2019 | USA | 1500 | |||||||
| 12 | Blueberry | 08/08/2019 | USA | 1500 | |||||||
| 13 | Mint | 08/04/2019 | USA | 2000 | |||||||
| 14 | Crunch | 08/05/2019 | USA | 100 | |||||||
| 15 | Blue Moon | 08/04/2019 | France | 500 | |||||||
| 16 | Butter Pecan | 09/05/2019 | France | 700 | |||||||
| 17 | Rocky Road | 09/05/2019 | France | 800 | |||||||
Sheet1 | |||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| I5 | =SUMPRODUCT(SUMIFS(D:D,B:B,F5,C:C,G5,A:A,H5:H8)) | |
Upvote
0
Power pivot would be the best way to handle both the complex question (query) and the amount of tables. It really is a no brainer. Also the flexibility of a pivot tables when tour boss says "now show me it this way" is so invaluable that its laughable.
Pivot tables might be a bit scary but they are worth the investment, I would argue they are almost as valuable as macros when you consder effort spent on solving a problem. What would take many hours coding and setting up pivot tables takes an hour or two to set Up.
Pivot tables might be a bit scary but they are worth the investment, I would argue they are almost as valuable as macros when you consder effort spent on solving a problem. What would take many hours coding and setting up pivot tables takes an hour or two to set Up.
Upvote
0
For me, when I do this, as soon as I type in H5:H8 it returns the value of 0. But if I type a single cell such as H5 then it returns correctly. Although this thread and the feedback from everyone has led to me realizing 2 things:
1) Given the type of data that I'm dealing with, it would be worth it for me to learn how to use pivot tables and some of the other more powerful queries. I hadn't before since we didn't really start out requiring the level of detail in the tables that we have now.
2) If I simply add the flavor category to the data on the 31 sheets where the raw data is added, then I can do SUMIFS for that column instead of for the flavor column.
[TABLE="width: 500"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Flavor[/TD]
[TD]Date[/TD]
[TD]Location[/TD]
[TD]Flavor Group[/TD]
[TD="align: right"]Total[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Oreos[/TD]
[TD="align: right"]08/01/2019[/TD]
[TD]USA[/TD]
[TD="align: right"]Cookies[/TD]
[TD="align: right"]1000[/TD]
[TD]Date[/TD]
[TD]Location[/TD]
[TD]Flavor Group[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Vanilla[/TD]
[TD="align: right"]08/02/2019[/TD]
[TD]USA[/TD]
[TD="align: right"]Vanillas[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]08/01/2019[/TD]
[TD]USA[/TD]
[TD]Chocolates[/TD]
[TD="align: right"]3500[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Chocolate[/TD]
[TD="align: right"]08/01/2019[/TD]
[TD]USA[/TD]
[TD="align: right"]Chocolates[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Strawberry[/TD]
[TD="align: right"]08/01/2019[/TD]
[TD]USA[/TD]
[TD="align: right"]Fruits[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]Double choc[/TD]
[TD="align: right"]08/04/2019[/TD]
[TD]USA[/TD]
[TD="align: right"]Chocolates[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]Pistachio[/TD]
[TD="align: right"]08/05/2019[/TD]
[TD]USA[/TD]
[TD="align: right"]Classic[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]Cream[/TD]
[TD="align: right"]08/01/2019[/TD]
[TD]USA[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]Raspberry[/TD]
[TD="align: right"]08/06/2019[/TD]
[TD]USA[/TD]
[TD="align: right"]Fruits[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]Blueberry[/TD]
[TD="align: right"]08/08/2019[/TD]
[TD]USA[/TD]
[TD="align: right"]Fruits[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD]Mint[/TD]
[TD="align: right"]08/04/2019[/TD]
[TD]USA[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD]Crunch[/TD]
[TD="align: right"]08/05/2019[/TD]
[TD]USA[/TD]
[TD="align: right"][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD]Blue Moon[/TD]
[TD="align: right"]08/04/2019[/TD]
[TD]France[/TD]
[TD="align: right"][/TD]
[TD="align: right"]500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD]Butter Pecan[/TD]
[TD="align: right"]09/05/2019[/TD]
[TD]France[/TD]
[TD="align: right"][/TD]
[TD="align: right"]700[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD]Rocky Road[/TD]
[TD="align: right"]09/05/2019[/TD]
[TD]France[/TD]
[TD="align: right"][/TD]
[TD="align: right"]800[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
Nonetheless, having to resort to this solution illustrates why I'm still a beginner in Excel
Will begin some studying on pivot tables.
Last edited:
Upvote
0
FormR
MrExcel MVP
- Joined
- Aug 18, 2011
- Messages
- 7,283
- Office Version
- 365
- Platform
- Windows
If you want to progress with this can you post the exact formula you ended up using and a small set of sample data that demonstrates the problem.
This sounds like a good way forward to me, especially if you don't get on well with the pivot tables.
Upvote
0
Similar threads
- Solved
