dated sum calculation

  • Thread starter Thread starter Todd
  • Start date Start date
T

Todd

I have a list of calculations that I need to sum if the
date of the column is earlier than today's date. The only
way I can think of to do this is cumbersome and has to
many ifs anyway. Can someone help me with a better way?

Here is what I have:

=IF(TODAY()>aa1,aa10)+IF(TODAY()>ac1,ac10)+etc.

for these 24 columns

=AA10+AC10+AE10+AG10+AI10+AK10+AM10+AO10+AQ10+AS10+AU10+AW1
0+AY10+BA10+BC10+BE10+BG10+BI10+BK10+BM10+BO10+BQ10+BS10+BU
10

The first cell in each column has the date. The rest have
the data I need to sum.


Thank you,


Todd
 
I think you're looking for something like this:

=SUMPRODUCT((MOD(COLUMN(AA1:BU1),2)=1)*(AA1:BU1<TODAY())
*AA10:BU10)

HTH
Jason
Atlanta, GA
 
Thanks Don, maybe I didn't understand but when I looked at
sumif but it doesn't seem to let me choose each column by
that columns date. Maybe I can describe this better?


A B C D
Todays Date()
1/01/03 01/015/03 02/01/03 02/15/03
8 9 10 11


For each row, sum row up to column date that is lower
than todays date. (Stop calculating at today's date -1).
 
I am playing with this and am still having trouble. I
don't understand how Jasons answer works. And I am stuck
on the sumif solution too. I have to have the sumif calc
whether todays date is higher than the column date.
Adding until I reach todays date. What I have is :

=SUMIF(A2:D2,TODAY(),A3:D3)

But it only adds if it IS today's date. but I can't get
worked out the part where it figures out to add if its
less than todays date

=SUMIF(A2:D2,<TODAY(),A3:D3) ?????

Thanks again,


Todd
 
Back
Top