Need to obtain sum of last X entries that are greater than 0

[Blue 58]

Extreme thanks in advance...

A1=20
A2=40
A3=10
A4=50
A5=0
A6=80
A7=0
A8=0

In the above example column, assuming that the last four >0 are
needed, the result would be A2+A3+A4+A6. If A7 is updated to
something >0, the result would be A3+A4+A6+A7.

 

[Biff]

Hi!

What if there aren't 4 values to sum?

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=SUM(A100:INDEX(A1:A100,LARGE(IF(A1:A100>0,ROW(A1:A100)),4)))

Use a big enough range to allow for added data. You can't use the entire
column, though.

Biff

 

[Ron Rosenfeld]

Extreme thanks in advance...

A1=20
A2=40
A3=10
A4=50
A5=0
A6=80
A7=0
A8=0

In the above example column, assuming that the last four >0 are
needed, the result would be A2+A3+A4+A6. If A7 is updated to
something >0, the result would be A3+A4+A6+A7.

The **array** entered formula:

=SUM(TRANSPOSE(OFFSET(rng,LARGE(ROW(rng)*(rng>0),{1,2,3,4})-1,0)))

should do that.

Enter this formula by holding down <ctrl><shift> while hitting <enter>. Excel
will place braces {...} around the formula if you did it correctly.


--ron