Sum last N numbers, ignore zeros

[Legacy 287389]

Hi good people!,

The closest I found was this formula:

=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?

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=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))))

 

[Legacy 287389]

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..

 

[Aladin Akyurek]

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

 

[RoryA]

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.

 

[Legacy 287389]

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...

 

[Legacy 287389]

RoryA,

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

 

[Aladin Akyurek]

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