How do I sum the last 5 nonzero numbers in a row?
 

I have values in a table and want to add a column on the right that will look
to the left at the previous 20 columns and find the last 5 numbers
(rightmost) that are not zero and sum them.

How do I sum the last 5 nonzero numbers in a row?
 

try this array formula, confirm by Ctrl, Shift and Enter

assume your data is in column A

=SUM(OFFSET($A$1,LARGE(IF($A$1:$A$100<0,ROW($A$1: $A$100)),5)-1,0,100,1))
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"crabatin" wrote:

I have values in a table and want to add a column on the right that will look
to the left at the previous 20 columns and find the last 5 numbers
(rightmost) that are not zero and sum them.

How do I sum the last 5 nonzero numbers in a row?
 

I have misread your request, you want to sum the last five non zero columns
instead of rows, try this array formula in U2 ( the 21st column), confirm by
Ctrl, Shift and Enter
=SUM(T2:INDEX(A2:T2,LARGE(COLUMN(A2:T2)*(A2:T2<0) ,5)))
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Francis" wrote:

try this array formula, confirm by Ctrl, Shift and Enter

assume your data is in column A

=SUM(OFFSET($A$1,LARGE(IF($A$1:$A$100<0,ROW($A$1: $A$100)),5)-1,0,100,1))
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"crabatin" wrote:

I have values in a table and want to add a column on the right that will look
to the left at the previous 20 columns and find the last 5 numbers
(rightmost) that are not zero and sum them.

How do I sum the last 5 nonzero numbers in a row?
 

Francis wrote:
try this array formula, confirm by Ctrl, Shift and Enter

assume your data is in column A

=SUM(OFFSET($A$1,LARGE(IF($A$1:$A$100<0,ROW($A$1: $A$100)),5)-1,0,100,1))

However, the OP said he wanted to sum the last 5 non-zero numbers in a ROW of 20
cells, not in a column, and he wanted that sum to be in the 21st column. When I
alter your formula for a row of 20 cells and put it to the right, I get a
circular reference, just like I got when I put your formula in A101. Any idea
how to fix that?

=SUM(OFFSET($A$1,0,LARGE(IF($A$1:$T$1<0,COLUMN($A $1:$T$1)),5)-1,1,20))

How do I sum the last 5 nonzero numbers in a row?
 

Francis wrote:
I have misread your request, you want to sum the last five non zero columns
instead of rows, try this array formula in U2 ( the 21st column), confirm by
Ctrl, Shift and Enter
=SUM(T2:INDEX(A2:T2,LARGE(COLUMN(A2:T2)*(A2:T2<0) ,5)))


Please ignore my post, this answers my question (and the OP's).

How do I sum the last 5 nonzero numbers in a row?
 

Yes this answers my question very well. Thank you for your help!

"Francis" wrote:

I have misread your request, you want to sum the last five non zero columns
instead of rows, try this array formula in U2 ( the 21st column), confirm by
Ctrl, Shift and Enter
=SUM(T2:INDEX(A2:T2,LARGE(COLUMN(A2:T2)*(A2:T2<0) ,5)))
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Francis" wrote:

try this array formula, confirm by Ctrl, Shift and Enter

assume your data is in column A

=SUM(OFFSET($A$1,LARGE(IF($A$1:$A$100<0,ROW($A$1: $A$100)),5)-1,0,100,1))
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"crabatin" wrote:

I have values in a table and want to add a column on the right that will look
to the left at the previous 20 columns and find the last 5 numbers
(rightmost) that are not zero and sum them.

How do I sum the last 5 nonzero numbers in a row?
 

Thanks again for the help - I ran into a little snag.

After looking at this a little bit, I realized that this worked fine as long
as the data started in column A, but if it wasn't exactly right when the data
starts in column D. How can I modify it for any starting position?

"Francis" wrote:

I have misread your request, you want to sum the last five non zero columns
instead of rows, try this array formula in U2 ( the 21st column), confirm by
Ctrl, Shift and Enter
=SUM(T2:INDEX(A2:T2,LARGE(COLUMN(A2:T2)*(A2:T2<0) ,5)))
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Francis" wrote:

try this array formula, confirm by Ctrl, Shift and Enter

assume your data is in column A

=SUM(OFFSET($A$1,LARGE(IF($A$1:$A$100<0,ROW($A$1: $A$100)),5)-1,0,100,1))
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"crabatin" wrote:

I have values in a table and want to add a column on the right that will look
to the left at the previous 20 columns and find the last 5 numbers
(rightmost) that are not zero and sum them.

How do I sum the last 5 nonzero numbers in a row?
 

Try this array formula (commit with CTRL+SHIFT+ENTER):

=SUM(W1:INDIRECT(ADDRESS(ROW(),LARGE((D1:W1<0)*CO LUMN(D1:W1),5))))


crabatin wrote:
Thanks again for the help - I ran into a little snag.

After looking at this a little bit, I realized that this worked fine as long
as the data started in column A, but if it wasn't exactly right when the data
starts in column D. How can I modify it for any starting position?

"Francis" wrote:

I have misread your request, you want to sum the last five non zero columns
instead of rows, try this array formula in U2 ( the 21st column), confirm by
Ctrl, Shift and Enter
=SUM(T2:INDEX(A2:T2,LARGE(COLUMN(A2:T2)*(A2:T2<0) ,5)))
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Francis" wrote:

try this array formula, confirm by Ctrl, Shift and Enter

assume your data is in column A

=SUM(OFFSET($A$1,LARGE(IF($A$1:$A$100<0,ROW($A$1: $A$100)),5)-1,0,100,1))
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"crabatin" wrote:

I have values in a table and want to add a column on the right that will look
to the left at the previous 20 columns and find the last 5 numbers
(rightmost) that are not zero and sum them.