Prev: Macro - onAction argumentsNext: 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" 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? > . >  |  Next  |  Last Pages: 1 2 Prev: Macro - onAction argumentsNext: Freeze Column