Multiple Drop down sum ifs

[nathan.volker12]

Hello All,

I have this excel worksheet and cannot quite figure out a way to make this work. So on the dashboard I have 3 drop downs. First drops down is in A1, This is 2 letters which is initials, there are 3 different selections here. The second and third drop downs are corresponding to dates, The first one being a start date, and second one being an end date.

Listed down the worksheet is the name of different products that correlate to jobs. On the other sheets there are todd, lexi, brooke, and this has all the material that is assigned to them under their own worksheets.

What I am trying to do is populate the estimate and actual that you see on the dashboard into each corresponding cell down the list of products on the dashboard page. This would correspond to the drop downs. So if for example I selected TE in the first drop down, then in the start date I put 6/6/2019, and the third which is end date I put 6/23/2019 then the list below on the dash board would go to Todds page, and find all the estimated material within that date range and list them in the correct spot, then find all the actual numbers for material and list them in that column.

I have attached the workbook that I am using to make the visual easier.

[Mumps1]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Dashboard sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. This macro will be triggered automatically when you make a selection in cell A1 of the Dashboard sheet. So before you make a selection in A1, first select the start and end dates and then make the selection in A1.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, ws As Worksheet, desRng As Range, product As Range, fnd As Range
    Dim sDate As String, eDate As String
    sDate = Range("C1")
    eDate = Range("E1")
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set desRng = Range("A3:A" & LastRow)
    Select Case Target.Value
        Case "TE"
            Set srcWS = Sheets("Todd")
        Case "LF"
            Set srcWS = Sheets("Lexi")
        Case "BS1"
            Set srcWS = Sheets("Brooke")
    End Select
    With srcWS.Cells(1).CurrentRegion
        .AutoFilter Field:=4, Criteria1:=">=" & CDate(sDate), Operator:=xlAnd, Criteria2:="<=" & CDate(eDate)
    End With
    For Each product In desRng
        With srcWS.Cells(1).CurrentRegion
            .AutoFilter 2, product
            product.Offset(0, 1).Value = WorksheetFunction.Sum(srcWS.Range("E2", srcWS.Range("E" & srcWS.Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible))
            product.Offset(0, 2).Value = WorksheetFunction.Sum(srcWS.Range("F2", srcWS.Range("F" & srcWS.Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible))
        End With
    Next product
    srcWS.Cells(1).AutoFilter
    Application.ScreenUpdating = True
End Sub

[modytrane]

Here's a solution with formulas.

Typical formula used:

Formula is long because I couldn't figure out how to do a vlookup inside the sumproduct. You could possibly use sumifs instead.

See the attached file with the formulas entered.