Sum of last 10 entries greater than zero

[jaespera]

Hello, I've been searching for hours for a formula that will pull the last 10 entries in a column, but these entries would have to be greater than zero. Would really appreciate your help!

 

[jbeaucaire]

Assuming the values are in column A, adjust these ranges to encompass as many rows as needed, be sure to leave the starting row at 1, this is an array formula:

=SUM(INDEX($A$1:$A$100, LARGE(IF($A$1:$A$100>0, ROW($A$1:$A$100), ""), 10)):INDEX($A$1:$A$100, LARGE(IF($A$1:$A$100>0, ROW($A$1:$A$100), ""), 1)))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array. You'll know the array is active when curly braces { } appear around your formula.

 

[Aladin Akyurek]

Hello, I've been searching for hours for a formula that will pull the last 10 entries in a column, but these entries would have to be greater than zero. Would really appreciate your help!

A bit different approach:

1. If the nature of data is such that negative values cannot occur...

Control+shift+enter, not just enter:

=SUM(IF(ROW(A1:A20)>=LARGE(IF(ISNUMBER(1/A1:A20),ROW(A1:A20)),
  MIN(COUNT(1/A1:A20),10)),A1:A20))

2. If negative values are bound to occur...

Control+shift+enter:

=SUM(IF(ROW(A1:A20)>=LARGE(IF(ISNUMBER(1/A1:A20),IF(A1:A20>0,ROW(A1:A20))),
  MIN(COUNTIF(A1:A20,">0"),10)),IF(A1:A20>0,A1:A20)))

 

[jaespera]

Thank you guys sooo much!!