I will assume the Data sheet (with name Data) looks like:
A B C ............ L
1 222220 ..... 3333 ..... 596.35
2 222220 ..... 3330 ..... 254.98
3 222220 ..... 3330 ..... 1547.36
4 222229 ..... 3330 ..... 259.36
5 222229 ..... 33353 ..... 12.36
That the summary sheet layout is
A B C
1 222220 3330 (Total of data from column L on other worksheet)
2 222220 3333
3 222220 33353
2 222221 3330
4 222221 3330
5 222221 33353
6 222229 3330
7 222229 3333
8 222229 33353
In C1 to sum all the L-values for the corresponding A and B value
=SUMPRODUCT(--(Data!$A$1:$A$1000=A1),--(Data!$B$1:$B$1000=C1), $L$1:$L$1000)
This can be copied down the column to complete the table.
Adjust the ranges as needed. Beware that SUMPRODUCT cannot use full-column
references such as A:A.
I duplicated the data in A to make it easier to write and copy the formula.
Bt I agree it looks tidier with out. So we will use Conditional formatting.
Select all the A after A1 (so A2:A1000 or whatever) column entries
Use Format Condition Formatting
Set the dialog to Formula is: =A2=A1 and then set the font colour to white
(or whatever you cell background is).
So now when you have in A2 the same values as in A1 (or if A3 the same as in
A2,,,,) the entry will be hidden
For more on SUMPRODUCT see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email