ExcelBanter - Excel 2002: How to sum up in blocks ?

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Excel 2002: How to sum up in blocks ? (https://www.excelbanter.com/excel-discussion-misc-queries/184654-excel-2002-how-sum-up-blocks.html)

Mr. Low

Excel 2002: How to sum up in blocks ?

Dear Sir,

I have the following table to be sum up in block as illustrated.

A B C D

Block
Serial No Border Amount Sub total
1 CX2145 LR 250 250
2 CX2146 550
3 CX2147 620
4 CX2148 420
5 CX2149 LR 200 1,790
6 CX2150 650
7 CX2151 210
8 CX2152 LR 140 1,000
9 CX2153 360
10 CX2154 480
11 CX2155 860
12 CX2156 LR 960 2,660

Column B is the block separator "LR" that divides the block to be sum up.

May I know what formula I must input at cell D1 and copy downwards to get
the answers?

Thanks

Low

--
A36B58K641

Tom Hutchins

Excel 2002: How to sum up in blocks ?

One way...

In D2, enter this formula:
=IF(B1="LR",C2,IF(ISNUMBER(D1),D1+C2,C2))
and copy down column D as needed.

I don't recall if Excel 2002 supports conditional formatting. If it does,
select the cells in column D from D2 as far as needed. Select Conditional
Formatting from the Format menu. Change 'Cell Value Is' to 'Formula Is', then
enter this formula:
=(B2<"LR")
Click the Format button, and on the Font tab, change the color to white.
Click OK several times until you exit the Conditional Formatting dialog. All
the numbers in column D except the block totals should be invisible (because
the text color matches the background color).

If Excel 2002 doesn't have conditional formatting, enter this formula in E2:
=IF(B2="LR",D2,"")
and copy down as needed. Then hide column D.

Hope this helps,

Hutch

"Mr. Low" wrote:

Dear Sir,

I have the following table to be sum up in block as illustrated.

A B C D

Block
Serial No Border Amount Sub total
1 CX2145 LR 250 250
2 CX2146 550
3 CX2147 620
4 CX2148 420
5 CX2149 LR 200 1,790
6 CX2150 650
7 CX2151 210
8 CX2152 LR 140 1,000
9 CX2153 360
10 CX2154 480
11 CX2155 860
12 CX2156 LR 960 2,660

Column B is the block separator "LR" that divides the block to be sum up.

May I know what formula I must input at cell D1 and copy downwards to get
the answers?

Thanks

Low

--
A36B58K641

RagDyeR

Excel 2002: How to sum up in blocks ?

Say headers are in A1 to D2, and data starts in A3.

Enter this in D3:

=IF(B3="LR",SUM($C$3:C3)-SUMIF($B$2:B2,"LR",$D$2:D2),"")

And copy down as needed.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

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

I have the following table to be sum up in block as illustrated.

A B C D

Block
Serial No Border Amount Sub total
1 CX2145 LR 250 250
2 CX2146 550
3 CX2147 620
4 CX2148 420
5 CX2149 LR 200 1,790
6 CX2150 650
7 CX2151 210
8 CX2152 LR 140 1,000
9 CX2153 360
10 CX2154 480
11 CX2155 860
12 CX2156 LR 960 2,660

Column B is the block separator "LR" that divides the block to be sum up.

May I know what formula I must input at cell D1 and copy downwards to get
the answers?

Thanks

Low

--
A36B58K641

RagDyeR

Excel 2002: How to sum up in blocks ?

Should add a caveat to the above suggested formula.

It is very resource intensive.

Wouldn't recommend it for use on ranges in excess of 5,000 cells.

http://tinyurl.com/2xawjs

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"RagDyeR" wrote in message
...
Say headers are in A1 to D2, and data starts in A3.

Enter this in D3:

=IF(B3="LR",SUM($C$3:C3)-SUMIF($B$2:B2,"LR",$D$2:D2),"")

And copy down as needed.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

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

I have the following table to be sum up in block as illustrated.

A B C D

Block
Serial No Border Amount Sub total
1 CX2145 LR 250 250
2 CX2146 550
3 CX2147 620
4 CX2148 420
5 CX2149 LR 200 1,790
6 CX2150 650
7 CX2151 210
8 CX2152 LR 140 1,000
9 CX2153 360
10 CX2154 480
11 CX2155 860
12 CX2156 LR 960 2,660

Column B is the block separator "LR" that divides the block to be sum up.

May I know what formula I must input at cell D1 and copy downwards to get
the answers?

Thanks

Low

--
A36B58K641

Mr. Low

Excel 2002: How to sum up in blocks ?

Hello Tom,

Thanks for your formula.

It works well.

Best Regards

Low

--
A36B58K641

"Tom Hutchins" wrote:

One way...

In D2, enter this formula:
=IF(B1="LR",C2,IF(ISNUMBER(D1),D1+C2,C2))
and copy down column D as needed.

I don't recall if Excel 2002 supports conditional formatting. If it does,
select the cells in column D from D2 as far as needed. Select Conditional
Formatting from the Format menu. Change 'Cell Value Is' to 'Formula Is', then
enter this formula:
=(B2<"LR")
Click the Format button, and on the Font tab, change the color to white.
Click OK several times until you exit the Conditional Formatting dialog. All
the numbers in column D except the block totals should be invisible (because
the text color matches the background color).

If Excel 2002 doesn't have conditional formatting, enter this formula in E2:
=IF(B2="LR",D2,"")
and copy down as needed. Then hide column D.

Hope this helps,

Hutch

"Mr. Low" wrote:

Dear Sir,

I have the following table to be sum up in block as illustrated.

A B C D

Block
Serial No Border Amount Sub total
1 CX2145 LR 250 250
2 CX2146 550
3 CX2147 620
4 CX2148 420
5 CX2149 LR 200 1,790
6 CX2150 650
7 CX2151 210
8 CX2152 LR 140 1,000
9 CX2153 360
10 CX2154 480
11 CX2155 860
12 CX2156 LR 960 2,660

Column B is the block separator "LR" that divides the block to be sum up.

May I know what formula I must input at cell D1 and copy downwards to get
the answers?

Thanks

Low

--
A36B58K641

Mr. Low

Excel 2002: How to sum up in blocks ?

Dear Sir,

Thanks for the formula.

It works.

Low
--
A36B58K641

"RagDyeR" wrote:

Should add a caveat to the above suggested formula.

It is very resource intensive.

Wouldn't recommend it for use on ranges in excess of 5,000 cells.

http://tinyurl.com/2xawjs

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"RagDyeR" wrote in message
...
Say headers are in A1 to D2, and data starts in A3.

Enter this in D3:

=IF(B3="LR",SUM($C$3:C3)-SUMIF($B$2:B2,"LR",$D$2:D2),"")

And copy down as needed.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

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

I have the following table to be sum up in block as illustrated.

A B C D

Block
Serial No Border Amount Sub total
1 CX2145 LR 250 250
2 CX2146 550
3 CX2147 620
4 CX2148 420
5 CX2149 LR 200 1,790
6 CX2150 650
7 CX2151 210
8 CX2152 LR 140 1,000
9 CX2153 360
10 CX2154 480
11 CX2155 860
12 CX2156 LR 960 2,660

Column B is the block separator "LR" that divides the block to be sum up.

May I know what formula I must input at cell D1 and copy downwards to get
the answers?

Thanks

Low

--
A36B58K641

RagDyeR

Excel 2002: How to sum up in blocks ?

Appreciate the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

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

Thanks for the formula.

It works.

Low
--
A36B58K641

"RagDyeR" wrote:

Should add a caveat to the above suggested formula.

It is very resource intensive.

Wouldn't recommend it for use on ranges in excess of 5,000 cells.

http://tinyurl.com/2xawjs

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"RagDyeR" wrote in message
...
Say headers are in A1 to D2, and data starts in A3.

Enter this in D3:

=IF(B3="LR",SUM($C$3:C3)-SUMIF($B$2:B2,"LR",$D$2:D2),"")

And copy down as needed.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

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

I have the following table to be sum up in block as illustrated.

A B C D

Block
Serial No Border Amount Sub total
1 CX2145 LR 250 250
2 CX2146 550
3 CX2147 620
4 CX2148 420
5 CX2149 LR 200 1,790
6 CX2150 650
7 CX2151 210
8 CX2152 LR 140 1,000
9 CX2153 360
10 CX2154 480
11 CX2155 860
12 CX2156 LR 960 2,660

Column B is the block separator "LR" that divides the block to be sum up.

May I know what formula I must input at cell D1 and copy downwards to get
the answers?

Thanks

Low

--
A36B58K641

All times are GMT +1. The time now is 10:05 AM.