• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

Excel Formula

Hi,

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.

GISVPN
0
gisvpn
Asked:
gisvpn
  • 2
3 Solutions
 
Davy2270Commented:
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:
=YEAR(C2)&WEEKNUM(C2)
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.

Regards,
Davy


0
 
Rory ArchibaldCommented:
=SUMIF(Sheet1!A:A,">="&A1,Sheet1!B:B)-SUMIF(Sheet1!A:A,">"&B1,Sheet1!B:B)

should do it.
0
 
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 < ?

Regards,

GISVPN
0
 
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now