Summing based on dates
in [Functions]
|
Prev: Macro - onAction arguments
Next: Freeze Column
From: Mike on 10 Apr 2010 14:55 I go running and have a mileage log. I have a list of non-continuous dates in column A (i.e. I don't run every day), and mileage figures in column B. On a separate part of the worksheet I want to put a summary table that shows a summary by week. So the first column will be "weekending" date and the second column mileage for the last seven days. I can't work out how to put an automatic formula in this second column. Sure, I could do a manual formula that just sums up the 3 or 4 days in that week that I ran, but there must be a better way?
From: T. Valko on 10 Apr 2010 16:24 Try this... A1:A21 = dates B1:B21 = mileage E1:E? = week ending dates Enter this formula in F1: =SUMIF(A$1:A$21,">"&E1-7,B$1:B$21)-SUMIF(A$1:A$21,">"&E1,B$1:B$21) Format as General or Number Copy down as needed -- Biff Microsoft Excel MVP "Mike" <mikegandy(a)gmail.com> wrote in message news:c116ee5e-bb31-43af-9b03-614b256cb48a(a)e7g2000yqf.googlegroups.com... >I go running and have a mileage log. I have a list of non-continuous > dates in column A (i.e. I don't run every day), and mileage figures in > column B. > > On a separate part of the worksheet I want to put a summary table that > shows a summary by week. So the first column will be "weekending" date > and the second column mileage for the last seven days. I can't work > out how to put an automatic formula in this second column. Sure, I > could do a manual formula that just sums up the 3 or 4 days in that > week that I ran, but there must be a better way?
From: ajnmxx on 10 Apr 2010 17:30 Perfect! Thank you so much for your help, much appreciated.
From: JLatham on 10 Apr 2010 21:04 Ok, for everything on the same sheet, with run dates in A and run mileage in B, assuming 'week ending' date in column F (and formula to go into G) and this formula would be for row 2: =SUMPRODUCT(--($A:$A<=F2),--($A:$A>=F2-6),($B:$B)) "ajnmxx" wrote: > I go running and have a mileage log. I have a list of non-continuous > dates in column A (i.e. I don't run every day), and mileage figures > in > column B. > On a separate part of the worksheet I want to put a summary table > that > shows a summary by week. So the first column will be "weekending" > date > and the second column mileage for the last seven days. I can't work > out how to put an automatic formula in this second column. Sure, I > could do a manual formula that just sums up the 3 or 4 days in that > week that I ran, but there must be a better way? > . >
From: JLatham on 10 Apr 2010 21:08
Just an added thought: if you put your first 'week ending' date into row 2, say in cell F2 and the formula I provided into G2, then in F3 you could put =F2+7 then copy the formula from G2 into G3 and then fill the two formulas in F3:G3 on down the sheet to build a list of "week ending" dates and results very easily. "ajnmxx" wrote: > I go running and have a mileage log. I have a list of non-continuous > dates in column A (i.e. I don't run every day), and mileage figures > in > column B. > On a separate part of the worksheet I want to put a summary table > that > shows a summary by week. So the first column will be "weekending" > date > and the second column mileage for the last seven days. I can't work > out how to put an automatic formula in this second column. Sure, I > could do a manual formula that just sums up the 3 or 4 days in that > week that I ran, but there must be a better way? > . > |