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

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

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.

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

Can you paste the sample data here please?

HAve a look here for posting data to the forum

Sounds like a SUMPRODUCT would do the trick.

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

 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

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.

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

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)

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

did you try a PivotTable?

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

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.

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

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

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

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/4/2019USA1500
9Pistachio8/5/2019USA1500
10Cream8/6/2019USA1500
11Raspberry8/6/2019USA1500
12Blueberry8/8/2019USA1500
13Mint8/4/2019USA2000
14Crunch8/5/2019USA100
15Blue Moon8/4/2019France500
16Butter Pecan9/5/2019France700

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

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

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

10. ## 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/6/2019 USA 1500
9 Pistachio 8/6/2019 USA 1500
10 Cream 8/6/2019 USA 1500
11 Raspberry 8/6/2019 USA 1500
12 Blueberry 8/6/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))
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.