Sum last N numbers, ignore zeros

L

Legacy 287389

Guest
Hi good people!,

The closest I found was this formula:
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))))
 
Upvote 0
Hi Aladin Akyurek,

The formula is supposed to sum the last 5 numbers which are bigger than zero. Currently the formula counts the numbers..Thank you..
 
Upvote 0
Hi Aladin Akyurek,

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:
Upvote 0
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.
 
Upvote 0
No Aladin Akyurek,

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...
 
Upvote 0
No Aladin Akyurek,

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))))}
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top