# Thread: Can someone help me with this? Probably a beginner level formula. Thanks: 0 Likes: 0

1. ## Re: Can someone help me with this? Probably a beginner level formula.

I give up

Have a nice day

2. ## Re: Can someone help me with this? Probably a beginner level formula.

Originally Posted by kweaver
Doesn't my last formula (without the restrictions on the rows) do that?

Excel 2010
A B C D E F G H I
3 Flavor Date Location Total
4 Oreos 8/1/2019 USA 1000 Date Location Flavor Total
5 Vanilla 8/2/2019 USA 1000 9/5/2019 France 1500
6 Chocolate 8/3/2019 USA 1000
7 Strawberry 8/4/2019 USA 4000
8 Double choc 8/4/2019 USA 1500
9 Pistachio 8/5/2019 USA 1500
10 Cream 8/6/2019 USA 1500
11 Raspberry 8/6/2019 USA 1500
12 Blueberry 8/8/2019 USA 1500
13 Mint 8/4/2019 USA 2000
14 Crunch 8/5/2019 USA 100
15 Blue Moon 8/4/2019 France 500
16 Butter Pecan 9/5/2019 France 700
17 Rocky Road 9/5/2019 France 800
Sheet11

Worksheet Formulas
Cell Formula
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))
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.

3. ## Re: Can someone help me with this? Probably a beginner level formula.

Originally Posted by sandy666
I give up

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.

4. ## Re: Can someone help me with this? Probably a beginner level formula.

nothing wrong

all what you need is Append all single tables into one (Power Query) then create Pivot Table

5. ## Re: Can someone help me with this? Probably a beginner level formula.

Originally Posted by Python49
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

Sheet1

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

6. ## Re: Can someone help me with this? Probably a beginner level formula.

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.

7. ## Re: Can someone help me with this? Probably a beginner level formula.

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?

8. ## Re: Can someone help me with this? Probably a beginner level formula.

Originally Posted by FormR
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

Worksheet Formulas
Cell Formula
I5 =SUMPRODUCT(SUMIFS(D:D,B:B,F5,C:C,G5,A:A,H5:H8))
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.

A B C D E F G H I
1
2
3 Flavor Date Location Flavor Group Total
4 Oreos 08/01/2019 USA Cookies 1000 Date Location Flavor Group Total
5 Vanilla 08/02/2019 USA Vanillas 1000 08/01/2019 USA Chocolates 3500
6 Chocolate 08/01/2019 USA Chocolates 1000
7 Strawberry 08/01/2019 USA Fruits 4000
8 Double choc 08/04/2019 USA Chocolates 1500
9 Pistachio 08/05/2019 USA Classic 1500
10 Cream 08/01/2019 USA 1500
11 Raspberry 08/06/2019 USA Fruits 1500
12 Blueberry 08/08/2019 USA Fruits 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

Nonetheless, having to resort to this solution illustrates why I'm still a beginner in Excel Will begin some studying on pivot tables.

9. ## Re: Can someone help me with this? Probably a beginner level formula.

Originally Posted by Python49
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.

Originally Posted by Python49
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.