Excel 2002: How to sum up in batches ?

[Mr. Low]

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

 

[Jim Thomlinson]

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))

 

[Mr. Low]

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

 

[iliace]

Have you tried using a PivotTable?

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








- Show quoted text -

 

[Mr. Low]

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

 

[David Hilberg]

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

 

[Mr. Low]

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

 

[David Hilberg]

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