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

 

[Python49]

Also I think I see what you were referring to with your formula. The one you gave will give the totals for all the flavors on the list that have a certain date and location. But what if we want to return the totals of all the pans on a particular date/location but for only the top half of the list, and not the bottom?

 

[sandy666]

I'll ask again: what about PivotTable?

 

[Python49]

Sadly I'm not familiar enough with those to have tried it or know how to do that one so I'm not really sure. I've usually used SUMIFS, SUMPRODUCT, etc. I was hoping there would be a formula solution since I'm going to have quite a few flavors and dates to do this for and I'm not sure how the pivot table would be affected by that.

 

[Michael M]

I'm with @sandy666 on this one !!!
Time to learn Pivot tables....it's pretty simple
Date as filter
Location as columns
Flavor as Rows
Totals as values


I think a formula would be incredibly complicated once you started having 4, 5, .....9 flavours

OR
you could code it !!

 

[sandy666]

all your needs from the first post to now you can do in just PivotTable

enough to read about: Pivot Table and some more links there.

or even change your range to Excel Table (Ctrl+T) , add Totals Row and use filters from the headers row (no formula, no PivotTable)

 

[Python49]

From glancing over it briefly just now and then creating one, I notice that it only lets me select a range from one sheet/tab. The spreadsheet I'm working with has 31 sheets for each day of a given month. On each sheet are a list of flavors that were produced with their weight, where they're going, and the expected delivery date (the same as the above examples we were doing). I'm trying to create a summary sheet that has a table/chart for each location, which can show the total weight they were supposed to receive on a specific day, according to flavor groups.

So for instance the flavor groups might be Vanillas, Chocolates, Fruit Flavors, Classic Flavors, etc. The table I'm trying to set up will have a summary for each location that charts for them the total weight for each flavor they are to receive on each day, according to the data on the other 31 sheets which recorded what was produced and where it was going. Just mentioning this to give further context to know whether a pivot table will still assist with this. I could just give the total collective weight for all the flavors together (without grouping them) that a location is receiving to make it easy, but then it means if the numbers don't match, the manager will have to go through all of the flavors they received to find what's missing. I was trying to make it easier for them to know exactly which flavor group didn't match so they can find it faster.

Previously I already had it charted pretty easily according to the total weight each location received on a particular day, but this one additional criteria whereby it's conditional by a flavor group is what had me stuck.

 

[Michael M]

That's a quantum leap from your first post....and you thought it was a beginner formula ??
You might need to VBA the summary sheet...!

 

[sandy666]

or Power Query
or even Power Pivot

 

[Python49]

Also I wanted to note that I wanted the chart/table with the summary info for each location to be in table form so that it's easy for both the receiving location and the sending location to compare exact totals for what was sent and what was received with conditional highlighting when numbers on any particular day do not match for a particular flavor group. Ultimately, I was going to use the formula I was requesting in this thread to create a summary chart as follows:

Location: XXXXXXX

Date	Vanillas	Chocolates	Classic	Kids	Fruits
9/1/2019
9/2/2019
9/3/2019
9/4/2019
9/5/2019
9/6/2019

<tbody>
</tbody>

For each day of the month. The flavor groups will each have say 5-40 different flavors. The spreadsheet that's tracking whats produced according to the day has all the flavors individually listed out, as well as categorized/grouped, with delivery dates, where it's going, and the total weight.

 

[Python49]

That's a quantum leap from your first post....and you thought it was a beginner formula ??
You might need to VBA the summary sheet...!

Lol.
Well, yeah I thought there would be a function to solve for it. I figured since I already was able to get the totals according to all flavors with a particular delivery date/location, that there would be a simple way to isolate the same criteria to only a select portion of those flavors instead of all. Same thing but just applying it to select flavors instead of the entire list.