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

Python49

New Member
Joined
Aug 18, 2018
Messages
32
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
open
too long. I've toyed around with some SUMPRODUCT formulas but can't get one to sum multiple flavors with multiple qualifiers.

open
open
https://drive.google.com/open?id=13yEfHm5cbggEuCAjffk4UjVm4tonCB-4
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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:
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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
17Rocky Road9/5/2019France800
Sheet11
Cell Formulas
RangeFormula
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))
 
Last edited:
Upvote 0
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))
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top