Excel Question - "upkeep" column?

the_stranger's picture
Rank: Chimp | 7

I keep a bunch of data on every single time I go running, including date, pace, temperature, distance, etc. One of the pieces of data I keep track of is called "upkeep" and it is the sum of my distances ran over the past 30 days. Since I do not go running every single day I can't just use a simple formula to update the "upkeep" column [=sum(a3:a33)]. Also, since months consist of 27, 30, or 31 days, I can't just mentally sum the numbers from 1/20/09 to 2/20/09. So what I have been doing up to this point is the best solution I have thought of: I have conditional formatting on the dates that highlights them if they satisfy [ =A6>TODAY()-30 ]. Then, when I add a new entry I have to go through and manually sum up the distances of the highlighted cells (past 30 days).

I will comment on one thing, and then ask a question.

Comment: I know, I'm a loser and should be spending my time studying or doing something more productive. Oh well.

Question: Is there any way to remove the manual labor from updating the "upkeep" column? If it is possible, I would prefer a solution that doesn't involve macros since I mainly update this spreadsheet from my mac with office 2008 -- However, if it requires macros, that is fine.

Comments (5)

Mar 4, 2009

=SUMIF(A1:A7,">"&(OFFSET(B7,0,COLUMN(A1)-COLUMN(B1))-30),C1)

Column A - dates
Column B - upkeep
Column C - the stuff being summed

a1:a7 -> all dates, resize as necessary
b7 -> current item

replace columns as needed, resize references too.

Mar 4, 2009

1) there are automated programs to do this, you know
2) here is what I just wrote up, probably more convoluted than it has to be but you can play with it

Assume x refers to the line value of that cell
Enter in F1 =TODAY()
Enter in F2 =EDATE(F1,-1)
Enter in Column A =DATE(YYYY,MM,DD) (fill in appropriate value for that day)
Enter corresponding mileage in Column B
Program Column C as =EDATE(Ax,0) where x is the column value, ie =EDATE(A1,0), etc. Hide this column
Program Column D as =IF(CX>F2,"Recent","Old"). Hide this column.
In the spot where you want your upkeep column, enter =SUMIF(D1:D99999,"Recent",B1:B99999)

There are easier ways, you can streamline it once you do that. I'm pretty sure you can enter a straight sumif condition without the column D, but my excel was being pissy about that

Mar 4, 2009

Column A = Date
Column B = Upkeep
Column C = Summation

=if(today()-30<=A2,B2,0)
=if(today()-30<=A3,B3,0)
=if(today()-30<=A4,B4,0)
etc....

At the bottom of column C, sum the entire column.

Dacarez and Drex - I think your ways work, but I feel like mine is more simple. I admittedly need to learn to use the offset function, though.

Mar 4, 2009

Not sure if I'm understanding this correctly but:

add a column (you can hide it afterwards) so that upkeep is in column b. A3 will be =if(b3>today()-30,1,0). Then you can do the sum in column B i.e. =sumif(A3:A33,"=1",B3:B33)

Mar 4, 2009
Comment