Solved

# Excel Formula

Posted on 2011-10-06
333 Views
Hi,

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

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
Question by:gisvpn

LVL 3

Assisted Solution

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

LVL 85

Accepted Solution

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

should do it.
0

Author Comment

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

LVL 85

Assisted Solution

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

#### Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!