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]

If you want to progress with this can you post the exact formula you ended up using and a small set of sample data that demonstrates the problem.



This sounds like a good way forward to me, especially if you don't get on well with the pivot tables.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]Flavor[/TD]
[TD]Date[/TD]
[TD]Location[/TD]
[TD]Total[/TD]
[TD]Date [/TD]
[TD]Location[/TD]
[TD]Flavor[/TD]
[/TR]
[TR]
[TD]Oreos[/TD]
[TD]8/2/2019[/TD]
[TD]USA[/TD]
[TD]1000[/TD]
[TD]9/5/2019[/TD]
[TD]USA[/TD]
[TD]Oreos[/TD]
[/TR]
[TR]
[TD]Vanilla[/TD]
[TD]9/5/2019[/TD]
[TD]USA[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD]Vanilla[/TD]
[/TR]
[TR]
[TD]Chocolate[/TD]
[TD]9/5/2019[/TD]
[TD]USA[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD]Chocolate[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I tried: =SUMPRODUCT(SUMIFS(D:D,B:B,E2,C:C,F2,A:A,G2:G8)) --- This gives me 0
=SUMPRODUCT(SUMIFS(D:D,B:B,E2,C:C,F2,A:A,G2)) --- This gives me 1000 for Oreos total.

 

[Python49]

or you can go with a VBA solution where i think as long as all 31 sheets are the same it would be useful.
do you have a master list of flavors on a separate sheet by chance?

Yes, I've got a master list that's used with all the flavors and other details on a table which I use to index some data from on the 31 other sheets. I'm going to add the flavor groups to that and then index it on the 31 sheets so I can get the SUMIFS.

 

[Swayzy]

I can highly recommend the Book powerbi and powerpivot by Rob collie and Avichal Singh. It is really really good and it gave me alot of new skills. Learn this Book and you Will set yourself far above the "Excel champ" label.

 

[FormR]

I tried: =SUMPRODUCT(SUMIFS(D:D,B:B,E2,C:C,F2,A:A,G2:G8)) --- This gives me 0

That exact formula, with that exact sample data returns 2000 (as expected) for me .

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1	Flavor	Date	Location	Total	Date	Location	Flavor		2000
2	Oreos	08/02/2019	USA	1000	09/05/2019	USA	Oreos
3	Vanilla	09/05/2019	USA	1000			Vanilla
4	Chocolate	09/05/2019	USA	1000			Chocolate
Sheet1

Cell Formulas
Range	Formula
I1	=SUMPRODUCT(SUMIFS(D:D,B:B,E2,C:C,F2,A:A,G2:G8))

 

[DataBlake]

Yes, I've got a master list that's used with all the flavors and other details on a table which I use to index some data from on the 31 other sheets. I'm going to add the flavor groups to that and then index it on the 31 sheets so I can get the SUMIFS.

here is my VBA solution

try this:
1. Make a userform with two listboxes (ListBox1 and ListBox2) and a command button (CommandButton1)
2. Make sure ListBoxes have multi select enabled (set the MultiSelect option in the properies panel to 2)
3. open the userform's code via right click > view code
4. copy and paste the code below
6. Add a module to your VBA project and copy/paste the flAVA code
5. run macro "flAVA"

notes:
- This rough draft does not include date specificity. if you want to specify a date just confirm that this is what you're looking for and we'll adjust accordingly.
- Make sure your flavors are in column A of a sheet named "Master List" or change both the sheet name and the column ranges of userform_activate to the respective column/sheet flavors are held in
- Make sure your Locations are in column B of a sheet named "Master List" or change both the sheet name and the column of 'lastRow' to the respective column/sheet flavors are held in
- This will execute the code on every sheet except for Master List. You have not made anyone aware of any other sheets besides this and the 31 sheets. Add any additional sheets to the sheet exemption
- when selecting multiple flavors/locations in the listbox use ctrl + click
- the results will appear in columns D-F of "Master List". change as you see fit

see this workbook as an example
https://drive.google.com/file/d/1_e1zAI42Y-4p4sKkMrWBrpTV_rZ13_6W/view?usp=sharing

this goes into your UserForm1 code

Sub flavorTOWN()
Dim ws As Worksheet, os As Worksheet
Dim wsCOUNT As Long, lastRow As Long
Dim i As Long, x As Long, j As Long, p As Long
Dim flSELECT(), loSELECT(), sSheet As Variant
Dim sumFLAV As Variant
Dim dicLOCATION As Object, dicFLAVOR As Object

'set variables
Set os = Sheets("Master List")
lastRow = os.Range("A" & Rows.Count).End(xlUp).Row
Set dicLOCATION = CreateObject("scripting.dictionary")
Set dicFLAVOR = CreateObject("scripting.dictionary")

    x = 0
    'pass all selected flavors into array
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) = True Then
            ReDim Preserve flSELECT(x)
            flSELECT(x) = ListBox1.List(i)
            x = x + 1
        End If
    Next i
    
    x = 0
    'pass all selected locations into array
    For i = 0 To ListBox2.ListCount - 1
        If ListBox2.Selected(i) = True Then
            ReDim Preserve loSELECT(x)
            loSELECT(x) = ListBox2.List(i)
            x = x + 1
        End If
    Next i

    'size of the sums array
    ReDim sumFLAV(LBound(flSELECT) To UBound(flSELECT), LBound(loSELECT) To UBound(loSELECT))

    'use arrays to get sums
    For Each ws In Sheets
        If ws.Name <> "Master List" Then
        'or ws.name <>"OTHERSHEETS" then
            sSheet = ws.Range("A1").CurrentRegion.Value2
            
            For i = LBound(sSheet) To UBound(sSheet)
        For x = LBound(loSELECT) To UBound(loSELECT)
            If sSheet(i, 3) = loSELECT(x) Then
                For p = LBound(flSELECT) To UBound(flSELECT)
                    If sSheet(i, 1) = flSELECT(p) Then
                     sumFLAV(p, x) = sumFLAV(p, x) + sSheet(i, 4)
                    End If
                Next p
            End If
        Next x
    Next i
            
        End If
    Next ws
            

    'paste results to sheet
    x = 2
    For j = LBound(loSELECT) To UBound(loSELECT)
        os.Range("D" & x).Value = loSELECT(j)
            For p = LBound(flSELECT) To UBound(flSELECT)
                os.Range("E" & x).Value = flSELECT(p)
                os.Range("F" & x).Value = sumFLAV(p, j)
                x = x + 1
            Next p
    Next j
        


            
            



End Sub

Private Sub UserForm_Activate()
Dim ws As Worksheet
Dim flARY As Variant, loARY As Variant

Set ws = Sheets("Master List")
lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
flARY = ws.Range("A2:A" & lastRow).Value2
loARY = ws.Range("B2:B" & lastRow).Value2

ListBox1.List = flARY
ListBox2.List = loARY
End Sub

Private Sub CommandButton1_Click()
Call flavorTOWN
Unload UserForm1
End Sub

this goes into a module or just use .show in any method of opening the userform

Sub flAVA()
UserForm1.Show
End Sub