Sum variable range of cells using "today's" date as starting point

1
votes

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)

3
excelworksheet-function

3 Answers

1
votes

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))

0
votes

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)
-1
votes
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