Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|