Can someone help me with this? Probably a beginner level formula.

Python49

New Member
Joined
Aug 18, 2018
Messages
32
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
open
too long. I've toyed around with some SUMPRODUCT formulas but can't get one to sum multiple flavors with multiple qualifiers.

open
open
https://drive.google.com/open?id=13yEfHm5cbggEuCAjffk4UjVm4tonCB-4
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Doesn't my last formula (without the restrictions on the rows) do that?

Excel 2010
ABCDEFGHI
3FlavorDateLocationTotal
4Oreos8/1/2019USA1000DateLocationFlavorTotal
5Vanilla8/2/2019USA10009/5/2019France1500
6Chocolate8/3/2019USA1000
7Strawberry8/4/2019USA4000
8Double choc8/4/2019USA1500
9Pistachio8/5/2019USA1500
10Cream8/6/2019USA1500
11Raspberry8/6/2019USA1500
12Blueberry8/8/2019USA1500
13Mint8/4/2019USA2000
14Crunch8/5/2019USA100
15Blue Moon8/4/2019France500
16Butter Pecan9/5/2019France700
17Rocky Road9/5/2019France800

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet11

Worksheet Formulas
CellFormula
I5=IF(ISBLANK(H5),SUMIFS(D:D,B:B,F5,C:C,G5),SUMIFS(D:D,B:B,F5,C:C,G5,A:A,H5))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
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
I give up :devilish:

Have a nice day
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.
 
Upvote 0
nothing wrong :)

all what you need is Append all single tables into one (Power Query) then create Pivot Table
 
Upvote 0
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.

Hi, you can do it like this.


Excel 2013/2016
ABCDEFGHI
1
2
3FlavorDateLocationTotal
4Oreos08/01/2019USA1000DateLocationFlavorFormula
5Vanilla08/02/2019USA100008/01/2019USAOreos3500
6Chocolate08/01/2019USA1000Vanilla
7Strawberry08/01/2019USA4000Chocolate
8Double choc08/04/2019USA1500Cream
9Pistachio08/05/2019USA1500
10Cream08/01/2019USA1500
11Raspberry08/06/2019USA1500
12Blueberry08/08/2019USA1500
13Mint08/04/2019USA2000
14Crunch08/05/2019USA100
15Blue Moon08/04/2019France500
16Butter Pecan09/05/2019France700
17Rocky Road09/05/2019France800
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0
or you can go with a VBA solution where i think as long as all 31 sheets are the same it would be useful.
do you have a master list of flavors on a separate sheet by chance?
 
Upvote 0
Hi, you can do it like this.

Excel 2013/2016
ABCDEFGHI
1
2
3FlavorDateLocationTotal
4Oreos08/01/2019USA1000DateLocationFlavorFormula
5Vanilla08/02/2019USA100008/01/2019USAOreos3500
6Chocolate08/01/2019USA1000Vanilla
7Strawberry08/01/2019USA4000Chocolate
8Double choc08/04/2019USA1500Cream
9Pistachio08/05/2019USA1500
10Cream08/01/2019USA1500
11Raspberry08/06/2019USA1500
12Blueberry08/08/2019USA1500
13Mint08/04/2019USA2000
14Crunch08/05/2019USA100
15Blue Moon08/04/2019France500
16Butter Pecan09/05/2019France700
17Rocky Road09/05/2019France800

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
I5=SUMPRODUCT(SUMIFS(D:D,B:B,F5,C:C,G5,A:A,H5:H8))

<tbody>
</tbody>

<tbody>
</tbody>
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.

ABCDEFGHI
1
2
3FlavorDateLocationFlavor GroupTotal
4Oreos08/01/2019USACookies1000DateLocationFlavor GroupTotal
5Vanilla08/02/2019USAVanillas100008/01/2019USAChocolates3500
6Chocolate08/01/2019USAChocolates1000
7Strawberry08/01/2019USAFruits4000
8Double choc08/04/2019USAChocolates1500
9Pistachio08/05/2019USAClassic1500
10Cream08/01/2019USA1500
11Raspberry08/06/2019USAFruits1500
12Blueberry08/08/2019USAFruits1500
13Mint08/04/2019USA2000
14Crunch08/05/2019USA100
15Blue Moon08/04/2019France500
16Butter Pecan09/05/2019France700
17Rocky Road09/05/2019France800

<tbody>
</tbody>


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
For me, when I do this, as soon as I type in H5:H8 it returns the value of 0.

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.

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.

This sounds like a good way forward to me, especially if you don't get on well with the pivot tables.
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top