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

#### Python49

##### New Member
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.

Last edited:

#### Python49

##### New Member
 Flavor Date Location Total Oreo 8/1/2019 USA 100 Vanilla 8/2/2019 USA 1000 =SUMIFS(D4:D8,B2:B11,F6,C2:C11,G6,A2:A11,H6) Chocolate 8/3/2019 USA 1000 Strawberry 8/4/2019 USA 1000 Date Location Flavor Passion 8/4/2019 USA 1500 8/4/2019 USA Strawberry Double Choc 8/5/2019 USA 1500 Pistachio 8/6/2019 USA 1500 Cream 8/7/2019 USA 1500 Raspberry 8/8/2019 USA 1500 Blueberry 8/9/2019 USA 1500

<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
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
did you try a PivotTable?

#### kweaver

##### Well-known Member
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
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
Doesn't my last formula (without the restrictions on the rows) do that?

Last edited:

#### Michael M

##### Well-known Member
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
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

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

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