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