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

Python49 · Sep 16, 2019

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

sandy666 · Sep 17, 2019

I give up

Have a nice day

Python49 · Sep 17, 2019

kweaver said:
Doesn't my last formula (without the restrictions on the rows) do that?

Excel 2010

A B C D E F G H I
Flavor Date Location Total
Oreos USA
Vanilla USA
Chocolate USA
Strawberry USA
Double choc USA
Pistachio USA
Cream USA
Raspberry USA
Blueberry USA
Mint USA
Crunch USA
Blue Moon France
Butter Pecan France
Rocky Road France

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]8/1/2019[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Location[/TD]
[TD="align: center"]Flavor[/TD]
[TD="align: center"]Total[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]8/2/2019[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9/5/2019[/TD]
[TD="align: center"]France[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1500[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]8/3/2019[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]8/4/2019[/TD]

[TD="align: right"]4000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]8/4/2019[/TD]

[TD="align: right"]1500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]8/5/2019[/TD]

[TD="align: right"]1500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]8/6/2019[/TD]

[TD="align: right"]1500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]8/6/2019[/TD]

[TD="align: right"]1500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]8/8/2019[/TD]

[TD="align: right"]1500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]8/4/2019[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]8/5/2019[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]8/4/2019[/TD]

[TD="align: right"]500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]9/5/2019[/TD]

[TD="align: right"]700[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]9/5/2019[/TD]

[TD="align: right"]800[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet11

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I5[/TH]
[TD="align: left"]=IF(ISBLANK(H5),SUMIFS(D:D,B:B,F5,C:C,G5),SUMIFS(D:D,B:B,F5,C:C,G5,A:A,H5))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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.

Python49 · Sep 17, 2019

sandy666 said:
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.

sandy666 · Sep 17, 2019

nothing wrong

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

FormR · Sep 17, 2019

Python49 said:
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

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

Swayzy · Sep 17, 2019

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.

DataBlake · Sep 17, 2019

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?

Python49 · Sep 17, 2019

FormR said:
Hi, you can do it like this.

Excel 2013/2016

A B C D E F G H I
Flavor Date Location Total
Oreos USA Date Location Flavor Formula
Vanilla USA USA Oreos
Chocolate USA Vanilla
Strawberry USA Chocolate
Double choc USA Cream
Pistachio USA
Cream USA
Raspberry USA
Blueberry USA
Mint USA
Crunch USA
Blue Moon France
Butter Pecan France
Rocky Road France

<tbody>
[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]

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

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]08/01/2019[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]08/02/2019[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]08/01/2019[/TD]

[TD="align: right"]3500[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]08/01/2019[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]08/01/2019[/TD]

[TD="align: right"]4000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]08/04/2019[/TD]

[TD="align: right"]1500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]08/05/2019[/TD]

[TD="align: right"]1500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]08/01/2019[/TD]

[TD="align: right"]1500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]08/06/2019[/TD]

[TD="align: right"]1500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]08/08/2019[/TD]

[TD="align: right"]1500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]08/04/2019[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]08/05/2019[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]08/04/2019[/TD]

[TD="align: right"]500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]09/05/2019[/TD]

[TD="align: right"]700[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]09/05/2019[/TD]

[TD="align: right"]800[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]I5[/TH]
[TD="align: left"]=SUMPRODUCT(SUMIFS(D:D,B:B,F5,C:C,G5,A:A,H5:H8))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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.

FormR · Sep 17, 2019

Python49 said:
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.

Python49 said:
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.

sandy666 · Sep 17, 2019

worth to read:

PowerQuery aka Get&Transform (links):

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

Python49

New Member

sandy666

Banned - Rules violations

Excel Facts

Python49

New Member

Python49

New Member

sandy666

Banned - Rules violations

FormR

MrExcel MVP

Swayzy

Board Regular

DataBlake

Well-known Member

Python49

New Member

FormR

MrExcel MVP

sandy666

Banned - Rules violations

Similar threads

Forum statistics

Share this page

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

New Member

Banned - Rules violations

Excel Facts

New Member

New Member

Banned - Rules violations

MrExcel MVP

Board Regular

Well-known Member

New Member

MrExcel MVP

Banned - Rules violations

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock