Need help with a formula with zero

[Curtis]

If I had column A with numbers; like if A1=15; A2=10; A3=12; A4=5; A5=8;
A6=10; A7=10 when I sum all this together it total to B1=70. Question is if
A6=0 and rest are the same then b1=10, what I trying to do is if there any
zero then it only add after zero not before. If A5=0 then A6 and A7 adds to
20, any help on this?

 

[T. Valko]

Will there ever be more than 1 zero? What should happen if there is more
than 1 zero?

Assuming there will *always* be 1 zero:

=SUM(A7:INDEX(A1:A7,MATCH(0,A1:A7,0)))

 

[Teethless mama]

If there is one zero, or more than one zero. It will add after the last zero,
if there is no zero, then it will add a whole range

=IF(COUNTIF(A1:A7,0),SUMPRODUCT(INDIRECT("A"&MAX((A1:A7=0)*ROW(A1:A7))&":A7")),SUM(A1:A7))

 

[Rick Rothstein \(MVP - VB\)]

You post is not clear if A7 will always contain the last number in the
column or if that was simply used for example purposes. Here is a formula
that doesn't care...

=SUM(A:A)-SUMPRODUCT(A1:A999*(IF(ISNA(MATCH(0,A:A,0)),0,MATCH(0,A:A,0))>ROW(A1:A999)))

As long as there are less than 1000 values in Column A (increase the 999 if
you need to handle more values), it will return the sum from the **first**
zero it sees up until the last number in the column.

Rick

 

[Rick Rothstein \(MVP - VB\)]

I forgot to mention... if there is no zero anywhere in Column A, the formula
returns the sum of the entire column. If you don't want that, or if you know
there will always be at least one zero in the column, you can use this
shorter formula...

=SUM(A:A)-SUMPRODUCT(A1:A999*(MATCH(0,A:A,0)>ROW(A1:A999)))

which will return an #N/A error if no zero exists in Column A, otherwise it
works the same as the first formula I posted.

Rick

 

[Curtis]

Hope it does, I''ll try this out sometime to day. There can be two or three
or maybe no zero. These number are use as for hours worked end day, if they
had 24 hour off then it reset time to zero for dot rules. Thanks!