Sum cells that match a date range

    Hello.

    Conceptually, what I'm trying to do would be best accomplished by =SUM(CELL("address"):CELL("address")), but that doesn't work because it returns text instead of a valid (useable) cell address.

    Context: I have multiple date ranges which vary in the number of days. I have the cell with the date range pulling (concatination) from the month and day values of the start date and end date of the range, looking something like this:

    Date Range: 1/22 - 1/29
    Week Starts: 1/22/2010
    Week Ends: 1/29/2010
    Business Days: 6

    I have a calendar chart on a back sheet, because I need to account for holidays over multiple years, so setting up a NETWORKDAYS function would have actually be more work. The calendar chart was a quick copy paste affair:
    Day of week/Date/Workday?/Holiday?
    1 / 12/22/2008 / 1
    2 / 12/23/2008 / 1
    3 / 12/24/2008 / 1
    4 / 12/25/2008 / 0 / 1
    5 / 12/26/2008 / 1
    6 / 12/27/2008 / 0
    7 / 12/28/2008 / 0
    1 / 12/29/2008 / 1
    2 / 12/30/2008 / 1
    3 / 12/31/2008 / 1
    4 / 1/1/2009 / 0 / 1

    ...where the if/thens should be obvious (holidays overriding what would otherwise be workdays).

    So what I'm trying to do is basically a function where there is a vlookup of the start and end date that returns both cell addresses in the "workday?" column, and sums the resulting range.

    I've tried various combinations of MATCH, CELL, INDEX, ROW, VLOOKUP, & ADDRESS, but anything that returns the cell address returns it as text, which is not useable within a formula. I also tried grabbing the cell address into an intermediate cell trying to use those as a sum range, but that wouldn't fly either, because excel didn't recognize them as actual cell locations, just true text.

    I ended up using a nasty if/then workaround, but I'm going to face this again and it's ugly:
    =VLOOKUP('SMB Sales'!J6, 'Calendar Table'!$B$2:$C$2202, 2)+IF('SMB Sales'!J6+1<='SMB Sales'!J7, VLOOKUP('SMB Sales'!J6+1, 'Calendar Table'!$B$2:$C$2202, 2)+IF(J6+2<=J7, VLOOKUP('SMB Sales'!J6+2, 'Calendar Table'!$B$2:$C$2202, 2)+IF(J6+3<=J7, VLOOKUP('SMB Sales'!J6+3, 'Calendar Table'!$B$2:$C$2202, 2)+IF(J6+4<=J7, VLOOKUP('SMB Sales'!J6+4, 'Calendar Table'!$B$2:$C$2202, 2)+IF(J6+5<=J7, VLOOKUP('SMB Sales'!J6+5, 'Calendar Table'!$B$2:$C$2202, 2)+IF(J6+6<=J7, VLOOKUP('SMB Sales'!J6+6, 'Calendar Table'!$B$2:$C$2202, 2)+IF(J6+7<=J7, VLOOKUP('SMB Sales'!J6+7, 'Calendar Table'!$B$2:$C$2202, 2)+IF(J6+8<=J7, VLOOKUP('SMB Sales'!J6+8, 'Calendar Table'!$B$2:$C$2202, 2)+IF(J6+9<=J7, VLOOKUP('SMB Sales'!J6+9, 'Calendar Table'!$B$2:$C$2202, 2)+IF(J6+10<=J7, VLOOKUP('SMB Sales'!J6+10, 'Calendar Table'!$B$2:$C$2202, 2)+IF(J6+11<=J7, VLOOKUP('SMB Sales'!J6+11, 'Calendar Table'!$B$2:$C$2202, 2)+IF(J6+12<=J7, VLOOKUP('SMB Sales'!J6+12, 'Calendar Table'!$B$2:$C$2202, 2),),),),),),),),),),),),)

    Just horrific, and frankly a little embarrassing. Not to mention the last thing I'd want to reconstruct when faced with this again.

    Thoughts?

    Re: Lookup to return cell address in USEABLE format?


    I may be a little thick, and so got lost as your description of the problem progressed. However on the basis of the 1st paragraph, I'd suggest you go read the help entry on the INDIRECT function. It provides a cell reference based on an address string, which seems to be what you're looking for.

    Re: Sum cells that match a date range


    Talk about an eye-chart! I, among others, can't be bothered with the eye strain. So, here's an example of how to return a cell address from a look up.


    A1: a date
    F1:Fx = a range of dates


    =INDIRECT(ADDRESS(MATCH(A1,F1:F17,0),6))

    Re: Sum cells that match a date range


    Looks good, thanks guys. Sorry about the eye strain, I was trying to prevent the follow up question posts by giving more detail.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login