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

Python49

New Member
Joined
Aug 18, 2018
Messages
24
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
 

Python49

New Member
Joined
Aug 18, 2018
Messages
24
FlavorDateLocationTotal
Oreo8/1/2019USA100
Vanilla8/2/2019USA1000=SUMIFS(D4:D8,B2:B11,F6,C2:C11,G6,A2:A11,H6)
Chocolate8/3/2019USA1000
Strawberry8/4/2019USA1000DateLocationFlavor
Passion8/4/2019USA15008/4/2019USAStrawberry
Double Choc8/5/2019USA1500
Pistachio8/6/2019USA1500
Cream8/7/2019USA1500
Raspberry8/8/2019USA1500
Blueberry8/9/2019USA1500

<tbody>
</tbody>

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.
 
Last edited:

kweaver

Well-known Member
Joined
May 8, 2018
Messages
873
Office Version
365, 2010
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)
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,327
did you try a PivotTable?
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
873
Office Version
365, 2010
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

New Member
Joined
Aug 18, 2018
Messages
24
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

Well-known Member
Joined
May 8, 2018
Messages
873
Office Version
365, 2010
Doesn't my last formula (without the restrictions on the rows) do that?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><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></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Flavor</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Date</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Location</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Total</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Oreos</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8/1/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">USA</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1000</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Date</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Location</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Flavor</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Total</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Vanilla</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8/2/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">USA</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1000</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">9/5/2019</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">France</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1500</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Chocolate</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8/3/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">USA</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1000</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Strawberry</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8/4/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">USA</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4000</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Double choc</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8/4/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">USA</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1500</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Pistachio</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8/5/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">USA</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1500</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Cream</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8/6/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">USA</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1500</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Raspberry</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8/6/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">USA</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1500</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Blueberry</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8/8/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">USA</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1500</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Mint</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8/4/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">USA</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2000</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Crunch</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8/5/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">USA</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">100</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Blue Moon</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8/4/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">France</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">500</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Butter Pecan</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">9/5/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">France</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">700</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Rocky Road</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">9/5/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">France</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">800</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet11</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I5</th><td style="text-align:left">=IF(<font color="Blue">ISBLANK(<font color="Red">H5</font>),SUMIFS(<font color="Red">D:D,B:B,F5,C:C,G5</font>),SUMIFS(<font color="Red">D:D,B:B,F5,C:C,G5,A:A,H5</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,327
Office Version
2013
Platform
Windows
This without the flavours

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

This with a choice of flavours

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

Python49

New Member
Joined
Aug 18, 2018
Messages
24
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/6/2019USA1500
9Pistachio8/6/2019USA1500
10Cream8/6/2019USA1500
11Raspberry8/6/2019USA1500
12Blueberry8/6/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>
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.
 

Forum statistics

Threads
1,086,222
Messages
5,388,552
Members
402,121
Latest member
jgyurko

Some videos you may like

This Week's Hot Topics

Top