Summing Cells Right-to-Left until a Cell with Value "0" is detected

**Chubster** · 02-12-2012, 04:53 PM

Hi,

I have a single row of numbers using columns A1:U1 (row1).
The goal is to sum the cells from Right-to-Left "U1:A1" until a value "0" is detected; the sums totalled at that point are the result.

This formula provided to me works Left-to-Right "A1:U1".
=SUM(OFFSET(A1,,,,MATCH(0,A1:U1,0)))
But not in the direction Right-to-Left "U1:A1:" that's needed.

Here's a sample row:

11, 12, 3, 0, 8, 7, 8, 6, 15, 15, 20, 0, 10, 15, 20, 20, 10, 15, 0, 10, 19

In the above A1=11 and U1=19
The correct result would be "29" going Right-to-Left.
There can be multiple "0"s in the series but only the first one found matters.
It's possible there may be no "0"'s.

I plan on copying the formula for use on multiple rows.

Any help would be fabulous and appreciated.

Chubs

**OnErrorGoto0** · 02-12-2012, 05:03 PM

Hi,
Something like

=SUM(INDEX(1:1,1,lookup(2,1/(A1:U1=0),column(A1:U1))):U1)

**Pete_UK** · 02-12-2012, 05:10 PM

This array* formula:

=SUM(INDIRECT(CHAR(64+MAX(IF(A1:U1=0,COLUMN(A1:U1))))&"1:U1"))

finds the last cell that contains a zero in your range, and then sums the cells from there to U1, so although it is actually summing left to right, it is only doing so from the last cell that contains a zero, so it does what you want.

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual <Enter>. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar, but you should not type these yourself. If you need to edit the formula subsequently, then you will need to use CSE again.

Hope this helps.

Pete

**Chubster** · 02-12-2012, 05:27 PM

Rory,
Just an amazing solution. Worked perfectly!
Thank you very much for such a fast turn-around and most importantly, a terrific answer.

Have a great day,
Chubs