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:

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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))))
 
L

Legacy 287389

Guest
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,133
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

Legacy 287389

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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))))}
 

Watch MrExcel Video

Forum statistics

Threads
1,123,141
Messages
5,599,969
Members
414,353
Latest member
ljhan

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
Top