Sum a dynamic range, with start date specified in another cell

**rw2** · 06-24-2016, 10:25 AM

I want to sum numbers over a six month period, but have the start month of six month period be changeable in another cell.

I've attached an example of what I mean.

The cell that specifies the start month is A3.

If A3 says Jan-15, I would like to sum from Jan-15 to Jun-15, and get an answer of 2287.
If A3 says Aug-15, I would like to sum from Oct-15 to Mar-15, and get an answer of 2568.

I can think how to use index/match to get the start month, but I don't know how to put this in a sum formula.

Thanks

**jason.b75** · 06-24-2016, 10:33 AM

Try

=SUM(OFFSET(E2,MATCH(A3,D3:D19,0),,6))

edit:-

With index, you could use

=SUM(INDEX(E3:E19,MATCH(A3,D3:D19,0)):INDEX(E3:E19,MATCH(A3,D3:D19,0)+5))

But it would return an error if the start months was less than 6 months from the end of the data.

**rw2** · 06-24-2016, 10:54 AM

Thanks! That works pefectly

Sum a dynamic range, with start date specified in another cell

LinkBack

Thread Tools

Rate This Thread

Display

Sum a dynamic range, with start date specified in another cell

Re: Sum a dynamic range, with start date specified in another cell

Re: Sum a dynamic range, with start date specified in another cell

Thread Information

Users Browsing this Thread

Similar Threads

Dynamic ranges with different start date

[SOLVED] Dynamic Chart with start date and end date

[SOLVED] Start date on pull down list being dynamic

Dynamic Name range for a Table should start from Row #3

[SOLVED] Dynamic named range based on a start date and end date

dynamic date range filter from cell value in vba

[SOLVED] select dynamic range with dynamic start point

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions