Sum of last 3 numbers in a range with gaps.

**donjoe** · 01-08-2011, 04:19 PM

I have a range where some cells contain numbers and other (random) cells are empty. Can I calculate the sum of the last 3 numbers in that range with a single formula? What would such a formula have to look like? (Preferably, the formula will continue to work if I later expand the range and add new numbers and new gaps at the end.)

Thanks.

**zbor** · 01-08-2011, 04:40 PM

Can you upload example excel workbook?

**donjoe** · 01-08-2011, 04:40 PM

Never mind, found it on the Net. It's an array formula and for a range of Y5:Y11 it looks like this:

{=SUM(INDEX(Y5:Y11;SMALL(IF(Y5:Y11<>"";ROW(Y5:Y11);"");COUNT(Y5:Y11)-3+1)-ROW(Y5)+1):Y11)}

**Ron Coderre** · 01-08-2011, 04:55 PM

For a non-array formula...

Referencing a specific range:

Please Login or Register  to view this content.

Referencing an entire column:

Please Login or Register  to view this content.

Sum of last 3 numbers in a range with gaps.

LinkBack

Thread Tools

Rate This Thread

Display

Sum of last 3 numbers in a range with gaps.

Re: Sum of last 3 numbers in a range with gaps.

Re: Sum of last 3 numbers in a range with gaps.

Re: Sum of last 3 numbers in a range with gaps.

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions