another array question

[Robert Dieckmann]

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

 

[Don Guillett]

try this. Does NOT need array entering.

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

 

[Epinn]

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.

Do a sort based on column A.

Highlight the entire range including the headings. Click Data>Subtotals .......
at each change in: color, use function: count, add subtotal to: color etc. etc.

Just a thought. Hope this helps.

I wonder how many formulae you need if you hardcode color and size as criteria in the formulae. I may be missing something here.

Epinn

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

 

[Epinn]

I like Pivot Table even more.

If you need help, post back.

Epinn

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.

Do a sort based on column A.

Highlight the entire range including the headings. Click Data>Subtotals .......
at each change in: color, use function: count, add subtotal to: color etc. etc.

Just a thought. Hope this helps.

I wonder how many formulae you need if you hardcode color and size as criteria in the formulae. I may be missing something here.

Epinn

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

 

[Epinn]

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 Data>Filter>Advanced Filter>Unique 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 special>transpose to place the unique list of color in row 1.

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

Epinn

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.

Do a sort based on column A.

Highlight the entire range including the headings. Click Data>Subtotals .......
at each change in: color, use function: count, add subtotal to: color etc. etc.

Just a thought. Hope this helps.

I wonder how many formulae you need if you hardcode color and size as criteria in the formulae. I may be missing something here.

Epinn

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