Excel 2007 : SUM Dynamic Range when Row is Compared to Column

**goldenr1** · 08-16-2011, 11:53 AM

I'm decent with the formula side of Excel. This is proving to be very difficult, though.

Here's what I am trying to do:

I have rows upon rows of data, usually in the form of a 1 or 2, spread out across 80-90 columns.

Each column that has data represents an individual Month - the month since the data value was created (not super important to know/understand).

The second-to-last column is the age of the row, in months.

The last column is what I am looking for - the SUM of the last 12 months of data for each row.

This means that for each row in the last column I need it to SUM for the range of cells in that row where the Month in the column is greater than or equal to the age of the row minus 12, but less than or equal to the age of that row.

I hope this is clear, it's as clear as I can explain it. Please see attached, and good luck! (also thank you, this will save me lots of work and ease my mind)

**ConneXionLost** · 08-16-2011, 12:21 PM

Hi,

How about:

Please Login or Register  to view this content.

Copied/Autofilled down.

Cheers,

**goldenr1** · 08-16-2011, 12:40 PM

That worked beautifully, my hat is off to you! I did not even think to use INDEX.

There is a small issue though - When the age is 10 or less (I changed the formula to actually only subtract 11, instead of 12) it produces the N/A error. Is there a way to circumvent this issue?

Again - thank you SO much!

**ConneXionLost** · 08-16-2011, 12:54 PM

Hmm, the only way I could reproduce the error was to reduce the age to less than 5. Since row 2 only goes up to 88, I suspect that is the problem.

One solution would be:

Please Login or Register  to view this content.

Cheers,

**goldenr1** · 08-16-2011, 01:03 PM

Thanks again.

That will remove the error code, but it still doesn't report the correct number. I suspect that the addition of columns (outside of the INDEX range) at the beginning of the data is what bumps it from 5 to 10 in my case. Regardless, it's now down to a reasonable number, and I can use a set of IF statements to hammer out the 1-10 aged items.

Keep up the awesome work.