Dynamic Range in Sum Function

[Lesa Muir]

Hi all

I want to be able to retrive the sum of a range of cells based on a date entered. Depending on the date entered the range of cells to sum will change.

EG: If date entered is Nov 08, then I want the sum of Jan 08 - Nov 08 data to sum so I get a Year to Date value returned.

I have a range of dates going out monthly to 2016.

EG

Jan 08 Feb 08 Mar 08......Dec 16 - (Range)
22.00 100.00 15.00

Date Entered = Mar 08

Sum = Jan, Feb and March (137.00)

If Date entered was Dec 16, it would only sum up Jan 16 - Dec 16 for a YTD value.

Appreciate any tips

[ffffloyd]

Would the SUMIF() function help you?

For example, if column A contained the range of dates, column B had the corresponding values and you entered the upper date limit in $D$3, the sum would be:

=SUMIF($A:$A,"<="&$D$3,$B:$B)

That doesn't take into account the starting date though. There is probably a nifty way of incorporating a start date into the SUMIF() function too but I would probably opt for the simple solution of using another column.

To do this, add some detail to the above example. In cell $D$2 we'll hold the starting date and determine that with the formula =DATE(YEAR($D$3),1,1).

Now in column C, enter this on row 2. (You could use row 1 instead but I'm going to assume you have column headings in that row):

=IF(AND(ISNUMBER($A2),ISNUMBER($B2)),IF($A2>=$D$2,$B2,0),"")

Then copy that formula down column C as far as your values go. This will only transfer values into column C if they are on or after the start date. (In fact, you could make the formula only transfer them if they were between the two dates. That would be neater -- then you could just do a simple SUM() -- but this is about using the SUMIF() function.) So now you could make that function:

=SUMIF($A:$A,"<="&$D$3,$C:$C)

[davesexcel]

Check out the example at this thread to see a formula to get values between dates.
http://www.excelforum.com/excel-gene...-by-month.html