I wonder how many formulae you need if you hardcode color and size as criteria in the formulae.

I figure out how to use *one* formula and a lot of dragging to give you the result you want.

Use DataFilterAdvanced FilterUnique Records Only to prepare a list of size "ID....." and a list of color respectively.

Say your data range is A1:B100 with column headings in row 1.

Column D (D1 is column heading) is your list of unique sizes and E1 and across is your list of color.

E2: =SUMPRODUCT(($A2:$A100=$D2)*($B2:$B100=E$1))

Then drag down and across.

Please note that you need to use paste specialtranspose to place the unique list of color in row 1.

I verified the result of my formula with a PivotTable. Bang on!

Epinn

"Epinn" wrote in message ...
Haven't read your other post.

Here is a quick alternative to using formulae. If you want formulae, stop reading now.

Insert column headings, say "Size" for column A and "Color" for column B.