Page 4 of 4 FirstFirst ... 234
Results 31 to 35 of 35

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

  1. #31
    New Member
    Join Date
    Aug 2018
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by FormR View Post
    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. #32
    New Member
    Join Date
    Aug 2018
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by BlakeSkate View Post
    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. #33
    Board Regular
    Join Date
    Mar 2018
    Posts
    55
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #34
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,089
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

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

    Quote Originally Posted by Python49 View Post
    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))

    Last edited by FormR; Sep 18th, 2019 at 03:00 AM.
    [code]your code[/code]

  5. #35
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Python49 View Post
    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_e1...ew?usp=sharing

    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
    Unload UserForm1
    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
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •