# Sum last N numbers, ignore zeros

L

#### Legacy 287389

##### Guest
Hi good people!,

HTML:
``=SUM(INDIRECT("ED"&INDEX(ROW(ED:ED),MAX((ED:ED<>"")*(ROW(ED:ED))))&":ED"&INDEX(ROW(ED:ED),MAX((ED:ED<>"")*(ROW(ED:ED))))-2))``

It does work, but it does not disregard zeros. Usually the zeros will be at the bottom of the array in a column. I need the formula in other words to add the last 5 numbers which are bigger than zero. Any help will be greatly appreciated. Thank you kindly..

EDIT: The array stretches only from ED3:ED14..maybe this will make things less complicated?

Last edited by a moderator:

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Control+shift+enter, not just enter:
Rich (BB code):
``````=SUM(IF(ROW(ED3:ED14)>=LARGE(IF(ISNUMBER(ED3:ED14),
IF(ED3:ED14>0,ROW(ED3:ED14))),MIN(5,COUNTIFS(ED3:ED14,">0"))),
IF(ISNUMBER(ED3:ED14),IF(ED3:ED14>0,1))))
``````

The formula is supposed to sum the last 5 numbers which are bigger than zero. Currently the formula counts the numbers..Thank you..

The formula is supposed to sum the last 5 numbers which are bigger than zero. Currently the formula counts the numbers..Thank you..

Doesn't it do just that?

 Row\Col ED​ EE​ EF​ 2​ 3​ 0​ 3​ 4​ 0​ 5​ 2​ 6​ 0​ 7​ 3​ 8​ 9​ 0​ 10​ 3​ 11​ 12​ 13​ -4​ 14​

<tbody>
</tbody>

EF3:

{=SUM(IF(ROW(ED3:ED14)>=LARGE(IF(ISNUMBER(ED3:ED14),IF(ED3:ED14>0,ROW(ED3:ED14))),MIN(5,COUNTIFS(ED3:ED14,">0"))),IF(ISNUMBER(ED3:ED14),IF(ED3:ED14>0,1))))}

Last edited:
Perhaps this:

=IF(COUNTIF(ED3:ED14,">0")<5,SUM(ED3:ED14),SUM(INDEX(ED:ED,LARGE(IF(ED3:ED14<>0,ROW(ED3:ED14)),5)):ED14))
array-entered.

It must SUM, not count..so the value in your example should sum to 8. I do appreciate your help, I think just somewhere something is not clear...

RoryA,

That's the one...Thank you very much!!

It must SUM, not count..so the value in your example should sum to 8. I do appreciate your help, I think just somewhere something is not clear...

Just replace 1 with the range to sum...

{=SUM(IF(ROW(ED3:ED14)>=LARGE(IF(ISNUMBER(ED3:ED14),IF(ED3:ED14>0,ROW(ED3:ED14))),MIN(5,COUNTIFS(ED3:ED14,">0"))),IF(ISNUMBER(ED3:ED14),IF(ED3:ED14>0,ED3:ED14))))}

Replies
9
Views
974
Replies
9
Views
1K
Replies
6
Views
2K
Replies
5
Views
374
Replies
7
Views
548

1,216,406
Messages
6,130,397
Members
449,581
Latest member
econtent2

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back