Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum a range of cells starting from an offset date list from TODAY()
Hi
I'm trying to sum a range of cells in a column adjacent to a column with dates starting with a cell opposite todays date. I've tried two ways with no success. 1) using VLOOKUP, ADDRESS, MATCH, etc in various combinations to try and acertain the reference of the 1st cell opposite todays date, so as to use that in the first part of SUM(??:B372). eg ADDRESS(VLOOKUP(TODAY(),$A7:$N$372,COLUMN(),FALSE) ,COLUMN()) I've even tried getting the row reference and column reference seperately and tried to join them together. 2) using SUMIF(A7:A38,A5,B7:B372) which gives me the value of the cell opposite today (which is in cell A5). I've tried "A5" but this just gives zero. This must be easy!! but I just can't see how to do it. The SUMIF function would appear to be the neatest but it doesn't seem to want to accept "if the value is greater than TODAY()". Is there any other way. I've been trying for a day now and have almost given up. Regards Jeff |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum a range of cells starting from an offset date list from TODAY(
Your sum range and criteria range should be the same size, try
=SUMIF(A7:A372,""&A5,B7:B372) " wrote: Hi I'm trying to sum a range of cells in a column adjacent to a column with dates starting with a cell opposite todays date. I've tried two ways with no success. 1) using VLOOKUP, ADDRESS, MATCH, etc in various combinations to try and acertain the reference of the 1st cell opposite todays date, so as to use that in the first part of SUM(??:B372). eg ADDRESS(VLOOKUP(TODAY(),$A7:$N$372,COLUMN(),FALSE) ,COLUMN()) I've even tried getting the row reference and column reference seperately and tried to join them together. 2) using SUMIF(A7:A38,A5,B7:B372) which gives me the value of the cell opposite today (which is in cell A5). I've tried "A5" but this just gives zero. This must be easy!! but I just can't see how to do it. The SUMIF function would appear to be the neatest but it doesn't seem to want to accept "if the value is greater than TODAY()". Is there any other way. I've been trying for a day now and have almost given up. Regards Jeff |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum a range of cells starting from an offset date list from TODAY(
Hi
Yes I realised I'd got the cell reference wrong after posting. However I've tried the ""&A5 changing the incorrect ref, but still get zero. A5 is todays date as a number. I've tried the date as 05 Nov 06 format the same as the column of data i'm looking for, but still no luck. However I woke up this morning and realised I can use an IF + AND statement in an adjacent column eg =IF(AND($A7$A$3,C7=1),1,0) & then sum this column instead. I'll just hide this column. (A3 has the cell with TODAY as the same format as the cells below. It doesn't work with "todays" date formatted as a number and the rest of the dates as 05 Nov 06 format. Strange. Thanks anyway dll. I'm sure there must be a way but I've got to get this finished by tomorrow. Jeff |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum a range of cells starting from an offset date list from TO
OK jeff,
for future reference.... that formula SHOULD work as long as you have dates in A7:A372 and a date in A5 then the format shouldn't matter. Without referencing a cell you could just use =SUMIF(A7:A372,""&TODAY(),B7:B372) If this still doesn't work then it may be because your numbers in column C are, in fact, text. What do you get using a formula like =ISNUMBER(B7)? If this gives FALSE then you probably need to convert column B to numeric. You can do this by selecting the column and using Data Text to Columns Finish " wrote: Hi Yes I realised I'd got the cell reference wrong after posting. However I've tried the ""&A5 changing the incorrect ref, but still get zero. A5 is todays date as a number. I've tried the date as 05 Nov 06 format the same as the column of data i'm looking for, but still no luck. However I woke up this morning and realised I can use an IF + AND statement in an adjacent column eg =IF(AND($A7$A$3,C7=1),1,0) & then sum this column instead. I'll just hide this column. (A3 has the cell with TODAY as the same format as the cells below. It doesn't work with "todays" date formatted as a number and the rest of the dates as 05 Nov 06 format. Strange. Thanks anyway dll. I'm sure there must be a way but I've got to get this finished by tomorrow. Jeff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
update cells to today date in a list of dates | Excel Worksheet Functions | |||
How do I add a range by date over 90 days older than today | Excel Discussion (Misc queries) | |||
Minimum Date Range from other cells | Excel Worksheet Functions | |||
Match function...random search? | Excel Worksheet Functions |