Sum variable range of cells using "today's" date as starting point
How do you sum a variable range of cells based upon today's date in MS Excel 2003.
Spreadsheet format:
Variable range = # of days to sum
Date range = listed in row 1, 1 day per cell (example A1=1/1/10, B1=1/2/10, C1=1/开发者_开发知识库3/10....)
Numbers to be summed - listed in row 2, X number per cell (example A2=8, B2=6, C2=1.....)
example problem: IF variable range = 2 & Current Date = 1/2/10 then...Sum(b2:c2)=7
I am able to sum the entire row based upon current date using the following formula but am not able to add the variable range to the sum function. =SUMIF(A1:C1,">="&TODAY(),A2:C2)
Assuming that the number of days to sum is in A4 and the date you want to start the sum from is in B4 try this formula
=SUM(OFFSET($A$2,0,MATCH($B$4,$1:$1,0)-1,1,$A$4))
As far as I can tell, there is no way to do a compound criteria inside of a SUMIF function. You could, if this isn't prohibitive, use an additional column to calculate a bool value to determine which cells should be summed, and use your existing SUMIF against that criteria.
i.e. $A$5 = The # of days in this case This assumes, that given today's date (6/21/2010) and $A$5 = 2, that you want the sum of all values in Row 2 that fall inclusively in the date range 6/21/2010-6/23/2010.
A3 =IF(A1 >= TODAY(), IF(A1 <= TODAY()+$A$5, TRUE, FALSE), FALSE)
Note: $A$5 is set as an absolute reference - this function would be safe to copy to B3 and C3.
Finally, your SUMIF would look like this:
=SUMIF(A3:C3,TRUE,A2:C2)
DATE 1/1/2010 1/1/2010 1/1/2010 1/1/2010 1/1/2010 1/1/2010 1/1/2010 1/2/2010 1/2/2010 1/2/2010 1/2/2010 1/2/2010 1/2/2010 1/2/2010
TIME 0:00 0:30 1:00 1:30 2:00 2:30 3:00 0:00 0:30 1:00 1:30 2:00 2:30 3:00
NO 1 2 3 4 5 6 7 3 4 5 6 7 8 9
VARIABLE RANGE 6
DATE 1/1/2010
TIME 2:00
ANS SHOULD BE 30
精彩评论