Excel Formula

Posted on 2011-10-06
Last Modified: 2012-05-12

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.

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:
    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.


    LVL 85

    Accepted Solution


    should do it.

    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 < ?


    LVL 85

    Assisted Solution

    by:Rory Archibald
    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.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    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…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    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…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now