Count every group of numbers whose sum is zero & put formula next toeach number

[al]

I have the following numbers in column A
1
2
3
-2
-4
5
5
-10
3
3
-6

I need a formula in column B next to each number telling me to which
group of sum zero it belows i.e

1,2,3,-2,-4 would each have number 1 next to each of them in column B
(first group of sum zero)

5,5,-10 would each have number 2 next each of them in column B (second
group of sum zero)

3,3,-6 would each have number 3 next to each of them in column B
(third group of sun zero)

Pls help thxs

 

[Shane Devenshire]

Hi,

Here is a way to do it with 2 columns, it probably can be done with 1 but...

Assume your data start in A2, in B2 enter

=IF(SUM(A$2:A2)<>0,1)

in C2 enter

=COUNTIF(B$2:B2,B1=TRUE)+B2

Copy down as far as necessary. Hide column B if desired.

 

[al]

I have the following numbers in column A
1
2
3
-2
-4
5
5
-10
3
3
-6

I need a formula in column B next to each number telling me to which
group of sum zero it belows i.e

1,2,3,-2,-4 would each have number 1 next to each of them in column B
(first group of sum zero)

5,5,-10 would each have number 2 next each of them in column B (second
group of sum zero)

3,3,-6 would each have number 3 next to each of them in column B
(third group of sun zero)

Pls help thxs

thxs shane !!! - exactly what i needed - do you know a bit about vba
macro -
can you give me a general macro which would achieve the same result if
i select the range of cells adjacent to the numbers sum of which need
to be zero - thxs

 

[al]

Hi,

Here is a way to do it with 2 columns, it probably can be done with 1 but...

Assume your data start in A2, in B2 enter

=IF(SUM(A$2:A2)<>0,1)

in C2 enter

=COUNTIF(B$2:B2,B1=TRUE)+B2

Copy down as far as necessary. Hide column B if desired.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

shane tried to combine the above formula into one but in vain - can
you pls help to get the formula into one column only txs

 

[Ashish Mathur]

Hi,

You can try this as well:

1. Assign a heading to the range of nos., say Numbers in cell C4;
2. Type Number in cell D4;
3. In cell F5, enter the following formula =ISNUMBER(C5);
4. In cell D5, type 1;
5. In cell D5, use the following formula =DSUM($C$4:C6,D$4,$F$4:$F$5) and
copy down;
6. In cell E5, use the following formula =COUNTIF($D$44,"0")+1 and copy
down

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[al]

Hi,

You can try this as well:

1. Assign a heading to the range of nos., say Numbers in cell C4;
2. Type Number in cell D4;
3. In cell F5, enter the following formula =ISNUMBER(C5);
4. In cell D5, type 1;
5. In cell D5, use the following formula =DSUM($C$4:C6,D$4,$F$4:$F$5) and
copy down;
6. In cell E5, use the following formula =COUNTIF($D$44,"0")+1 and copy
down

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com

ashish i'm looking for something using formulas in 1 column only - can
you help pls

 

[Bernd P]

Hello,

If A1 and B1 are empty and your data starts at A2, enter into B2:
=IF(ABS(SUM(A$1:A1))<0.0000000001,B1+1,B1)
and copy down.

Regards,
Bernd

 

[al]

Hello,

If A1 and B1 are empty and your data starts at A2, enter into B2:
=IF(ABS(SUM(A$1:A1))<0.0000000001,B1+1,B1)
and copy down.

Regards,
Bernd

great thxs a lot for that improved formula