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

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

Originally Posted by FormR
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.
 A B C D E F G Flavor Date Location Total Date Location Flavor Oreos 8/2/2019 USA 1000 9/5/2019 USA Oreos Vanilla 9/5/2019 USA 1000 Vanilla Chocolate 9/5/2019 USA 1000 Chocolate

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.

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

Originally Posted by BlakeSkate
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.

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

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.

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

Originally Posted by Python49
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
ABCDEFGHI
1FlavorDateLocationTotalDateLocationFlavor2000
2Oreos08/02/2019USA100009/05/2019USAOreos
3Vanilla09/05/2019USA1000Vanilla
4Chocolate09/05/2019USA1000Chocolate

Sheet1

Worksheet Formulas
CellFormula
I1=SUMPRODUCT(SUMIFS(D:D,B:B,E2,C:C,F2,A:A,G2:G8))

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

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

this goes into your UserForm1 code
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
End Sub```
this goes into a module or just use .show in any method of opening the userform
Code:
```Sub flAVA()
UserForm1.Show
End Sub```