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

Question

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)

Answer 1

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)