Excel Formula


I have the following spreadsheet which I'll refer to as Spreadsheet A

 Excel Capture 1
and this spreadsheet which I will refer to as Spreadsheet B

 Excel Capture 2
I would like to write a formula which sits on each row on Spreadsheet B (i.e. row 3 downwards) and using the dates in column A and B on the same row as the formula sits looks at Column A of spreadsheet A and matches all dates that fall between the two. I would like then for all the dates that are matched on Spreadsheet A to total up all of the hours that in column B in Spreadsheet A and just show the figure.

As an example If I had the formula in column C, row 4 it would match rows 7 and 10 on Spreadsheet A and the formula would return the total number 10 (as it adds 9 and 1).

Is this possible through SUMPRODUCT or similar?

Any help would be greatly appreciated.

Who is Participating?
Rory ArchibaldCommented:

should do it.
You should be using the function WEEKNUM.
This function is often not standard available, so you might have to install the Analysis-Toolpak addin. Developertab - Add-Ins, then check Analysis Toolpak
Now, weeknum alone won't be sufficient as your list has multiple calendar years.

Then you type the following formula into cell C1 on sheet A:
Now you have a key on which you can use the SUMIF function.
Then fill down the formula for the whole list. Take note that excel starts its weeks on Sunday, your weekbuckets in sheet B start on Mondays.


gisvpnAuthor Commented:
Thanks Rorya - that works perfect.

Could you explain why the second SUMIF (SUMIF(Sheet1!A:A,">"&B1,Sheet1!B:B)) has a ">" I would have expected it to be < ?


Rory ArchibaldCommented:
The first one adds everything greater than or equal to the start date, so the second one has to subtract anything greater than the end date to just leave the bit between the two dates.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.