Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002: How to add without splitting blocks ? | Excel Discussion (Misc queries) | |||
Excel must display error message if two blocks don't have same num | Excel Worksheet Functions | |||
how do I create random blocks of letter and words in excel? | Excel Worksheet Functions | |||
print in blocks in excel | Excel Discussion (Misc queries) | |||
Moving blocks of data in excel | Excel Discussion (Misc queries) |