Need help with a formula with zero
 

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?

Need help with a formula with zero
 

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)))

--
Biff
Microsoft Excel MVP


"Curtis" wrote in message
...
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?

Need help with a formula with zero
 

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))


"Curtis" wrote:

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?

Need help with a formula with zero
 

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,MATC H(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


"Curtis" wrote in message
...
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?

Need help with a formula with zero
 

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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,MATC H(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


"Curtis" wrote in message
...
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?

Need help with a formula with zero
 

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!

"Rick Rothstein (MVP - VB)" wrote:

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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,MATC H(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


"Curtis" wrote in message
...
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?