Thanks to those who responded to my previous post. On the same spreadsheet
(Excel 2000) I have these values in columns A & B

ID1220 - gray
ID1220 - white
ID1624 - gray
ID1220 - white
ID1624 - white
ID1220 - red

Here I am trying to find out how many units I have of the various colors of
a particular size. What I want to know is:
ID1220 - gray = 1
ID1220 - white = 2
ID1220 - red = 1

Again, I am assuming this has to be done in an array but I know very little
about them.
Thanks in advance.

Robert

try this. Does NOT need array entering.

=sumproduct((rngA="id1220")*(rngB="gray"))

--
Don Guillett
SalesAid Software

"Robert Dieckmann" wrote in message
...
Thanks to those who responded to my previous post. On the same
spreadsheet (Excel 2000) I have these values in columns A & B

ID1220 - gray
ID1220 - white
ID1624 - gray
ID1220 - white
ID1624 - white
ID1220 - red

Here I am trying to find out how many units I have of the various colors
of a particular size. What I want to know is:
ID1220 - gray = 1
ID1220 - white = 2
ID1220 - red = 1

Again, I am assuming this has to be done in an array but I know very
little about them.
Thanks in advance.

Robert

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.

I like Pivot Table even more.

If you need help, post back.

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.

=SUMPRODUCT(--(A1:A100="ID1220"),--(B1:B100="gray"))


"Robert Dieckmann" wrote:

Thanks to those who responded to my previous post. On the same spreadsheet
(Excel 2000) I have these values in columns A & B

ID1220 - gray
ID1220 - white
ID1624 - gray
ID1220 - white
ID1624 - white
ID1220 - red

Here I am trying to find out how many units I have of the various colors of
a particular size. What I want to know is:
ID1220 - gray = 1
ID1220 - white = 2
ID1220 - red = 1

Again, I am assuming this has to be done in an array but I know very little
about them.
Thanks in advance.

Robert

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.