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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.


Rory ArchibaldCommented:

should do it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.