Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
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 | |||
Excel formulas to create large blocks of text | Excel Discussion (Misc queries) | |||
print in blocks in excel | Excel Discussion (Misc queries) | |||
Moving blocks of data in excel | Excel Discussion (Misc queries) |