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

[Python49]

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

 

[Michael M]

Can you paste the sample data here please?

HAve a look here for posting data to the forum

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Sounds like a SUMPRODUCT would do the trick.

 

[Python49]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Flavor[/TD]
[TD]Date[/TD]
[TD]Location[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oreo[/TD]
[TD]8/1/2019[/TD]
[TD]USA[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Vanilla[/TD]
[TD]8/2/2019[/TD]
[TD]USA[/TD]
[TD]1000[/TD]
[TD]=SUMIFS(D4:D8,B2:B11,F6,C2:C11,G6,A2:A11,H6)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chocolate[/TD]
[TD]8/3/2019[/TD]
[TD]USA[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Strawberry[/TD]
[TD]8/4/2019[/TD]
[TD]USA[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]Location[/TD]
[TD]Flavor[/TD]
[/TR]
[TR]
[TD]Passion[/TD]
[TD]8/4/2019[/TD]
[TD]USA[/TD]
[TD]1500[/TD]
[TD][/TD]
[TD]8/4/2019[/TD]
[TD]USA[/TD]
[TD]Strawberry[/TD]
[/TR]
[TR]
[TD]Double Choc[/TD]
[TD]8/5/2019[/TD]
[TD]USA[/TD]
[TD]1500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pistachio[/TD]
[TD]8/6/2019[/TD]
[TD]USA[/TD]
[TD]1500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cream[/TD]
[TD]8/7/2019[/TD]
[TD]USA[/TD]
[TD]1500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Raspberry[/TD]
[TD]8/8/2019[/TD]
[TD]USA[/TD]
[TD]1500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blueberry[/TD]
[TD]8/9/2019[/TD]
[TD]USA[/TD]
[TD]1500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I know how to get the sumtotal for all the strawberrys on the list according to date and locations, but I want to be able to get the total for multiple flavors according to date and location. Found it faster to just type the details as an example here than post screenshot.

 

[kweaver]

Am I over-simplifying what you want with this: =SUMIFS(D4:D12,B4:B12,F5,C4:C12,G5) which will exclude the flavor and just look for the date and location match.

Or, if the table increases: =SUMIFS(D:D,B:B,F5,C:C,G5)

 

[sandy666]

did you try a PivotTable?

 

[kweaver]

You might also put an I column next to your criteria table and use this: =IF(ISBLANK(H5),SUMIFS(D:D,B:B,F5,C:C,G5),SUMIFS(D:D,B:B,F5,C:C,G5,A:A,H5)) in I5.

 

[Python49]

Am I over-simplifying what you want with this: =SUMIFS(D4:D12,B4:B12,F5,C4:C12,G5) which will exclude the flavor and just look for the date and location match.

Or, if the table increases: =SUMIFS(D:D,B:B,F5,C:C,G5)

I will have a list of say 100 flavors actually and would like to take the totals for various combinations of flavors from a certain date for a certain location. So out of 100 flavors on a list (all with their own date, location, and total) I'd want say the total for 5 of the flavors that all match a particular date and location. So in the table above, I'd need a formula that can tell me the total for both strawberry and passion since they're both having the same date and location (total would be 2500)

 

[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

Cell Formulas
Range	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))

 

[Michael M]

This without the flavours

=SUMPRODUCT(--(B2:B15=F3)*(C2:C15=G3)*(D2:D15))

This with a choice of flavours

=SUMPRODUCT(--(B2:B15=F3)*(C2:C15=G3)*(A2:A15=H3)*(D2:D15))

 

[Python49]

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/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"]9[/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"]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/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"]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]

It's possible I didn't explain what I meant well but in your table here, what I'm trying to get is a formula which will let me get the total for a select number of flavors on the list and get the total. So in your above table, I want to be able to select all the flavors which need to be included in the totals. For instance, if we were to use flavors 4-9 only (oreos to pistachio), I'd want to know the total for only those flavors, which have a date of 8/6/2019 and location of USA. The total would be 3000 for just double chocolate and pistachio. If we then change the selected flavors instead to 4-8 only (oreos to double choc) then now the total would just be 1500 for double choc. If we keep flavors 4-8 now and change the date to be 8/1/2019 then it would give a total of 1000 only for Oreos.

In the formula above, it seems to only be taking SUMIFS for one particular flavor, unless I'm doing it incorrectly.