Multiple Lookup and CountIF

[exxon99]

Hi Excel Experts,

I need a favour to figure out the logic behind this. For example my
table has 3fields,

Field 1 = CategoryID
Field 2 = Category
Field 3 = Item

The data is inserted into this table as raw data like below

6726 Stationary Pen
6726 Stationary Pen
6726 Stationary Pencil
5627 Utensils Spoon
5627 Utensils Knife

Another worksheet will summarize these details with fixed information
as below
but it will count the occurence of the 1st and 3rd field.
Pen Pencil Spoon Knife
6726 Stationary 2 1
5627 Utensils 1 1

I think it can be done using Vlookup and countif but am not sure how it
will be arranged.

Thanks for the help in advance

 

[Biff]

Hi!

This table is in the range A2:C6:

6726 Stationary Pen
6726 Stationary Pen
6726 Stationary Pencil
5627 Utensils Spoon
5627 Utensils Knife

C10:F10 = headers: Pen Pencil Spoon Knife
A11:B11 = 6726 Stationary

Enter this formula in C11:

=SUMPRODUCT(--($A$2:$A$6=$A11),--($C$2:$C$6=C$10))

Copy across then down as needed.

Biff

 

[AndyH]

Hi

I would use an array formula, which should work ok. something like

{=sum(if(a1:a500=cellreferenceforproductid,if(c1:c500=cellreferenceforitem,1,0)))}

if you send an e-mail address i'll drop you a sample

andy