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.

Please Login or Register  to view this content.

[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.