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.
=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.
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
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.
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)
Ea enim eligendi corrupti voluptatibus pariatur. Quidem perferendis aliquid dicta delectus qui. Sed totam consequatur totam aut nesciunt.
Libero id dolore maxime est assumenda et vel ducimus. Ratione sit accusamus praesentium velit deleniti. Omnis recusandae tempora rerum quos. Quisquam fuga dolorem aliquam dicta dicta et debitis. Quidem et saepe temporibus voluptatibus quo.
Eius eos natus necessitatibus. Ut aut ut et autem quis facere.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...