Excel 2002: How to add without splitting blocks ?
 

Dear Sir,

May I know if there is any formula to calculate sub total at column D other
than using a Pivot Table ?

A B C D
Group Code Amount Sub Total
1 B2 K920 100
2 B2 K920 200
3 B2 K920 350 650
4 B2 P356 400
5 B2 P356 200 600
6 B2 T478 140
7 B2 T478 130
8 B2 T478 120 390
9 B3 K920 110
10 B3 K920 90 200
11 B3 P356 120
12 B3 P356 180
13 B3 P356 90 390
14 B3 T478 80
15 B3 T478 60 140
16 B4 K920 40
17 B4 K920 160 200
18 B4 P356 200
19 B4 P356 100
20 B4 P356 160 460


Currently I split the table into groups and use the formula
:IF(B1<B2,SUMIF(B$1:B$8,B1,C$1:C$8),"") at D1 .

Can I do it without splitting?

Thanks


Low



--
A36B58K641

Excel 2002: How to add without splitting blocks ?
 

In D2 try:

=IF(B4<B5,SUM($C$3:C4)-SUM($E$3:E3),"")

and copy down as far as required.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Mr. Low" wrote in message
...
Dear Sir,

May I know if there is any formula to calculate sub total at column D
other
than using a Pivot Table ?

A B C D
Group Code Amount Sub Total
1 B2 K920 100
2 B2 K920 200
3 B2 K920 350 650
4 B2 P356 400
5 B2 P356 200 600
6 B2 T478 140
7 B2 T478 130
8 B2 T478 120 390
9 B3 K920 110
10 B3 K920 90 200
11 B3 P356 120
12 B3 P356 180
13 B3 P356 90 390
14 B3 T478 80
15 B3 T478 60 140
16 B4 K920 40
17 B4 K920 160 200
18 B4 P356 200
19 B4 P356 100
20 B4 P356 160 460


Currently I split the table into groups and use the formula
:IF(B1<B2,SUMIF(B$1:B$8,B1,C$1:C$8),"") at D1 .

Can I do it without splitting?

Thanks


Low



--
A36B58K641

Excel 2002: How to add without splitting blocks ?
 

Hi

You can cheat,

In D2 put the formula =C2
In D3 put the formula
=IF(B3=B2,C3+D2,C3)

Now the cheat. Select all the subtotals
Format|Conditional format|Formula is
enter the formula
=D2D1
Set the font colour to white and click OK
The intermediate numbers will still be there but you won't see them.

Mike

"Mr. Low" wrote:

Dear Sir,

May I know if there is any formula to calculate sub total at column D other
than using a Pivot Table ?

A B C D
Group Code Amount Sub Total
1 B2 K920 100
2 B2 K920 200
3 B2 K920 350 650
4 B2 P356 400
5 B2 P356 200 600
6 B2 T478 140
7 B2 T478 130
8 B2 T478 120 390
9 B3 K920 110
10 B3 K920 90 200
11 B3 P356 120
12 B3 P356 180
13 B3 P356 90 390
14 B3 T478 80
15 B3 T478 60 140
16 B4 K920 40
17 B4 K920 160 200
18 B4 P356 200
19 B4 P356 100
20 B4 P356 160 460


Currently I split the table into groups and use the formula
:IF(B1<B2,SUMIF(B$1:B$8,B1,C$1:C$8),"") at D1 .

Can I do it without splitting?

Thanks


Low



--
A36B58K641

Excel 2002: How to add without splitting blocks ?
 

A better conditional format would be
=B2=B3
and set the font to white

Mike

"Mike H" wrote:

Hi

You can cheat,

In D2 put the formula =C2
In D3 put the formula
=IF(B3=B2,C3+D2,C3)

Now the cheat. Select all the subtotals
Format|Conditional format|Formula is
enter the formula
=D2D1
Set the font colour to white and click OK
The intermediate numbers will still be there but you won't see them.

Mike

"Mr. Low" wrote:

Dear Sir,

May I know if there is any formula to calculate sub total at column D other
than using a Pivot Table ?

A B C D
Group Code Amount Sub Total
1 B2 K920 100
2 B2 K920 200
3 B2 K920 350 650
4 B2 P356 400
5 B2 P356 200 600
6 B2 T478 140
7 B2 T478 130
8 B2 T478 120 390
9 B3 K920 110
10 B3 K920 90 200
11 B3 P356 120
12 B3 P356 180
13 B3 P356 90 390
14 B3 T478 80
15 B3 T478 60 140
16 B4 K920 40
17 B4 K920 160 200
18 B4 P356 200
19 B4 P356 100
20 B4 P356 160 460


Currently I split the table into groups and use the formula
:IF(B1<B2,SUMIF(B$1:B$8,B1,C$1:C$8),"") at D1 .

Can I do it without splitting?

Thanks


Low



--
A36B58K641