Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to sum up in batches ?
Dear Sir,
Lets consider the following worksheet: A B C D 1 Product Invoice Amount Sub Total 2 A491 V4148 222 3 A491 V4149 1410 4 A491 V4150 282 1914 5 C225 V4151 672 6 C225 V4152 1344 2016 7 H247 V4153 118 8 H247 V4154 118 9 H247 V4155 427 10 H247 V4156 1332 1994 11 A491 V4157 120 12 A491 V4158 222 13 A491 V4159 113 14 A491 V4160 222 677 15 H247 V4161 118 16 H247 V4162 180 17 H247 V4163 222 520 18 C225 V4164 444 19 C225 V4165 528 20 C225 V4166 339 21 C225 V4167 1056 2366 22 D208 V4168 528 23 D208 V4169 328 24 D208 V4170 202 1057 25 Total 10544 10544 May I know what formula must I input at cell D2 and copy downward to sum up identical product in batches as illustrated ? These product codes repeats itself as I sort it in ascending order according to its dates. Thanks Low -- A36B58K641 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to sum up in batches ?
While I personally would use Data - Pivot Table or Data - Subototal to do
what you are asking here is a formula that you can put in column D =IF(A2=A3, "", SUMIF($A$2:$A$24, A2, $C$2:$C$24)) -- HTH... Jim Thomlinson "Mr. Low" wrote: Dear Sir, Lets consider the following worksheet: A B C D 1 Product Invoice Amount Sub Total 2 A491 V4148 222 3 A491 V4149 1410 4 A491 V4150 282 1914 5 C225 V4151 672 6 C225 V4152 1344 2016 7 H247 V4153 118 8 H247 V4154 118 9 H247 V4155 427 10 H247 V4156 1332 1994 11 A491 V4157 120 12 A491 V4158 222 13 A491 V4159 113 14 A491 V4160 222 677 15 H247 V4161 118 16 H247 V4162 180 17 H247 V4163 222 520 18 C225 V4164 444 19 C225 V4165 528 20 C225 V4166 339 21 C225 V4167 1056 2366 22 D208 V4168 528 23 D208 V4169 328 24 D208 V4170 202 1057 25 Total 10544 10544 May I know what formula must I input at cell D2 and copy downward to sum up identical product in batches as illustrated ? These product codes repeats itself as I sort it in ascending order according to its dates. Thanks Low -- A36B58K641 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to sum up in batches ?
Dear Sir,
I used Data Subtotal to sum up the batches. It works very well. Thank you for that tip. However when I used the formula =IF(A2=A3, "", SUMIF($A$2:$A$24, A2, $C$2:$C$24)), I could not get the right answer. I think this formula is only suitable for batches of reference that do not repeat itself. Is there any other formulas for this purpose ? Thanks Low -- A36B58K641 "Jim Thomlinson" wrote: While I personally would use Data - Pivot Table or Data - Subototal to do what you are asking here is a formula that you can put in column D =IF(A2=A3, "", SUMIF($A$2:$A$24, A2, $C$2:$C$24)) -- HTH... Jim Thomlinson "Mr. Low" wrote: Dear Sir, Lets consider the following worksheet: A B C D 1 Product Invoice Amount Sub Total 2 A491 V4148 222 3 A491 V4149 1410 4 A491 V4150 282 1914 5 C225 V4151 672 6 C225 V4152 1344 2016 7 H247 V4153 118 8 H247 V4154 118 9 H247 V4155 427 10 H247 V4156 1332 1994 11 A491 V4157 120 12 A491 V4158 222 13 A491 V4159 113 14 A491 V4160 222 677 15 H247 V4161 118 16 H247 V4162 180 17 H247 V4163 222 520 18 C225 V4164 444 19 C225 V4165 528 20 C225 V4166 339 21 C225 V4167 1056 2366 22 D208 V4168 528 23 D208 V4169 328 24 D208 V4170 202 1057 25 Total 10544 10544 May I know what formula must I input at cell D2 and copy downward to sum up identical product in batches as illustrated ? These product codes repeats itself as I sort it in ascending order according to its dates. Thanks Low -- A36B58K641 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to sum up in batches ?
Have you tried using a PivotTable?
On Aug 17, 10:24 am, Mr. Low wrote: Dear Sir, I used Data Subtotal to sum up the batches. It works very well. Thank you for that tip. However when I used the formula =IF(A2=A3, "", SUMIF($A$2:$A$24, A2, $C$2:$C$24)), I could not get the right answer. I think this formula is only suitable for batches of reference that do not repeat itself. Is there any other formulas for this purpose ? Thanks Low -- A36B58K641 "Jim Thomlinson" wrote: While I personally would use Data - Pivot Table or Data - Subototal to do what you are asking here is a formula that you can put in column D =IF(A2=A3, "", SUMIF($A$2:$A$24, A2, $C$2:$C$24)) -- HTH... Jim Thomlinson "Mr. Low" wrote: Dear Sir, Lets consider the following worksheet: A B C D 1 Product Invoice Amount Sub Total 2 A491 V4148 222 3 A491 V4149 1410 4 A491 V4150 282 1914 5 C225 V4151 672 6 C225 V4152 1344 2016 7 H247 V4153 118 8 H247 V4154 118 9 H247 V4155 427 10 H247 V4156 1332 1994 11 A491 V4157 120 12 A491 V4158 222 13 A491 V4159 113 14 A491 V4160 222 677 15 H247 V4161 118 16 H247 V4162 180 17 H247 V4163 222 520 18 C225 V4164 444 19 C225 V4165 528 20 C225 V4166 339 21 C225 V4167 1056 2366 22 D208 V4168 528 23 D208 V4169 328 24 D208 V4170 202 1057 25 Total 10544 10544 May I know what formula must I input at cell D2 and copy downward to sum up identical product in batches as illustrated ? These product codes repeats itself as I sort it in ascending order according to its dates. Thanks Low -- A36B58K641- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to sum up in batches ?
Hello iliace,
Yes, I did. I just like to know if there are some other ways of doing this other than Pivot Table and Data Sub Total Function. Kind Regards Low -- A36B58K641 "iliace" wrote: Have you tried using a PivotTable? On Aug 17, 10:24 am, Mr. Low wrote: Dear Sir, I used Data Subtotal to sum up the batches. It works very well. Thank you for that tip. However when I used the formula =IF(A2=A3, "", SUMIF($A$2:$A$24, A2, $C$2:$C$24)), I could not get the right answer. I think this formula is only suitable for batches of reference that do not repeat itself. Is there any other formulas for this purpose ? Thanks Low -- A36B58K641 "Jim Thomlinson" wrote: While I personally would use Data - Pivot Table or Data - Subototal to do what you are asking here is a formula that you can put in column D =IF(A2=A3, "", SUMIF($A$2:$A$24, A2, $C$2:$C$24)) -- HTH... Jim Thomlinson "Mr. Low" wrote: Dear Sir, Lets consider the following worksheet: A B C D 1 Product Invoice Amount Sub Total 2 A491 V4148 222 3 A491 V4149 1410 4 A491 V4150 282 1914 5 C225 V4151 672 6 C225 V4152 1344 2016 7 H247 V4153 118 8 H247 V4154 118 9 H247 V4155 427 10 H247 V4156 1332 1994 11 A491 V4157 120 12 A491 V4158 222 13 A491 V4159 113 14 A491 V4160 222 677 15 H247 V4161 118 16 H247 V4162 180 17 H247 V4163 222 520 18 C225 V4164 444 19 C225 V4165 528 20 C225 V4166 339 21 C225 V4167 1056 2366 22 D208 V4168 528 23 D208 V4169 328 24 D208 V4170 202 1057 25 Total 10544 10544 May I know what formula must I input at cell D2 and copy downward to sum up identical product in batches as illustrated ? These product codes repeats itself as I sort it in ascending order according to its dates. Thanks Low -- A36B58K641- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to sum up in batches ?
Mr. Low:
If you're still looking for a formula, I would modify Jim's answer as follows: =IF(A2=A3, "", SUMIF($A$2:A2, A2, $C$2:C2)) - David Jim Thomlinson wrote: =IF(A2=A3, "", SUMIF($A$2:$A$24, A2, $C$2:$C$24)) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to sum up in batches ?
Dear Sir,
I used the formula =IF(A2=A3, "", SUMIF($A$2:A2, A2, $C$2:C2)) in the table. Cell D4,D5 and D10 are correct, however D14,D17 and D21 are not correct as it also add up the figure of the previous block that has the same reference. Is there anyway to modify this formula ? Thanks Low A B C D 1 Product Invoice Amount subtotal 2 A491 V4148 222.00 3 A491 V4149 1,410.00 4 A491 V4150 282.00 1914 Correct 5 C225 V4151 672.00 6 C225 V4152 1,344.00 2016 Correct 7 H247 V4153 118.00 8 H247 V4154 118.00 9 H247 V4155 427.00 10 H247 V4156 1,332.00 1995 Correct 11 A491 V4157 120.00 12 A491 V4158 222.00 13 A491 V4159 113.00 14 A491 V4160 222.00 2591 Error 15 H247 V4161 118.00 16 H247 V4162 180.00 17 H247 V4163 222.00 2515 Error 18 C225 V4164 444.00 19 C225 V4165 528.00 20 C225 V4166 339.00 21 C225 V4167 1,056.00 4383 Error 22 D208 V4168 528.00 23 D208 V4169 328.00 24 D208 V4170 202.00 1058 correct -- A36B58K641 "David Hilberg" wrote: Mr. Low: If you're still looking for a formula, I would modify Jim's answer as follows: =IF(A2=A3, "", SUMIF($A$2:A2, A2, $C$2:C2)) - David Jim Thomlinson wrote: =IF(A2=A3, "", SUMIF($A$2:$A$24, A2, $C$2:$C$24)) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to sum up in batches ?
Mr. Low,
Yes - when this formula is entered in E2 and copied downward, it subtracts the previous subtotals, leaving only the current one. =IF(A2=A3, "", SUMIF($A$2:A2, A2, $C$2:C2) - SUMIF($A1:$A$2, A2, $E1:E$2)) I'm using Excel 2003, which automatically rearranges the dollar signs as necessary, so that in row 4 and below the subtracted function looks like this: .... - SUMIF($A$2:$A3, ... - David Mr. Low wrote: Dear Sir, I used the formula =IF(A2=A3, "", SUMIF($A$2:A2, A2, $C$2:C2)) in the table. Cell D4,D5 and D10 are correct, however D14,D17 and D21 are not correct as it also add up the figure of the previous block that has the same reference. Is there anyway to modify this formula ? Thanks Low A B C D 1 Product Invoice Amount subtotal 2 A491 V4148 222.00 3 A491 V4149 1,410.00 4 A491 V4150 282.00 1914 Correct 5 C225 V4151 672.00 6 C225 V4152 1,344.00 2016 Correct 7 H247 V4153 118.00 8 H247 V4154 118.00 9 H247 V4155 427.00 10 H247 V4156 1,332.00 1995 Correct 11 A491 V4157 120.00 12 A491 V4158 222.00 13 A491 V4159 113.00 14 A491 V4160 222.00 2591 Error 15 H247 V4161 118.00 16 H247 V4162 180.00 17 H247 V4163 222.00 2515 Error 18 C225 V4164 444.00 19 C225 V4165 528.00 20 C225 V4166 339.00 21 C225 V4167 1,056.00 4383 Error 22 D208 V4168 528.00 23 D208 V4169 328.00 24 D208 V4170 202.00 1058 correct |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 : Unable to open files in MS Outlook 2002 | Excel Discussion (Misc queries) | |||
Outlook 2002 calendar dates exported to Excel 2002 sort incorrectl | Excel Worksheet Functions | |||
Can't Unhide Rows in 'batches'! | Excel Discussion (Misc queries) | |||
Atomatic file name in excell 2002 from cel A1 (like in word 2002) | New Users to Excel | |||
Can you print labels using Excel 2002 in a Word 2002 mail merge? | Excel Discussion (Misc queries) |