Count values from cells depending on drop down selection

Hello Guys,

I have a sheet (sheet A) with a drop down that populates a report feeding from another sheet (sheet b) using data validation. On sheet A I keep track of the amount hours I spent daily on a project. but I need help finding a way for Sheet B to keep a track of the total amount of hours spent on every project monthy based on how many hours are used on Sheet A. (Please consider the projects on Sheet A are never populated in order.)
I've attached a couple of screenshots to help explain what I'm looking for.

Pictures are nice but one cannot work with them. Please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Pictures are nice but one cannot work with them. Please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

If I understand what you are after, enter this in Contracts list .xlsx L3 and fill down: (both files have to be open for the calculation)

Formula:

Please Login or Register  to view this content.

If it was my project, I wouldn't use a worksheet for each week nor would I use a separate workbook for the data and the report. I would have all the data and report in one workbook with the data in a table. I have done this using your supplied data and converting the 3 weeks into a single table and then using that table to extract the data required using this formula (table is on Sheet1)

Formula:

Please Login or Register  to view this content.

This will make more sense if you look at the workbook. As you add data, the table will extend and the formulae will automatically expand to accommodate the new data.

Using the table, you can easily produce a Pivot Table to create various reports and you can easily isolate each contract to see the data only for that contract or selected contracts...the possibilities are many without having to do a great deal of work to produce.

First: Sumif could have been used instead of Sumifs. The terms would just have to be re-arranged to match the requirements of SUMIF.

Here is a formula that will work to sum new worksheets in the separate workbook IF you create a list of the worksheet tabs in the Contracts list-1.xlsx workbook and give it a name. I named the list which I have in a table named Tabs.

Capture.JPG

The formula in K3 of Contracts list-1.xlsx would be like this:

Formula:

Please Login or Register  to view this content.

When you add a worksheet to the Weekly Billable....workbook, add the worksheet to the Contracts List workbook with this syntax
[Weekly Billable Time Sheet 2016 - Copy-1.xlsm]Week #34

Here are your workbooks with the formula summing across the range of worksheets.
I think that this is much more difficult to maintain but that is up to you.

Thank you New Doverman,

I actually started with a single book in a similar way as that one is, however it was not very effective for me for numerous reasons. That's how I ended with a separate workbook.
I tried using sumproduct but It didn't worked, now I see what my error was. I was not creating a list of tabs inside contracts list.xlsx. It now works like a charm!

Again as always I really appreciate your time and effort!

Thank you for the feedback.

If you follow the last upload that I gave you, you will notice that the tabs are listed in a table. This table will expand the defined name for the range so that you don't have to continually change the definition of the name.

Good luck.