Sum a range of cells starting from an offset date list from TODAY()

[jeff.taylor]

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

 

[daddylonglegs]

Your sum range and criteria range should be the same size, try

=SUMIF(A7:A372,">"&A5,B7:B372)

 

[jeff.taylor]

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

 

[daddylonglegs]

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