Matching data and returning count

[JJ]

I have a set of criteria values in Column A and would like to insert a formula into column B which matches them to a LOOKUP list and counts how many entries DO NOT meet a criteria

E.g. Counting the number of times cat, sat or mat (cell A1:A3) appears in the LOOKUP list when the value is NOT EQUAL to 0


TABLE
A B
1 cat *
2 sat *
3 mat *

LOOKUP LIST
Critria Value
cat 0
mat 1.5
mat 0
sat 2
cat 2
cat 3
sat 2


would return

A B

1 cat 2
2 sat 2
3 mat 1



Anyone help??

[Aladin Akyurek]

JJ,

Lets A2:A4 house

{"cat";
"sat";
"mat"}

and F1:G8

{"Criteria","Value";
"cat",0;
"mat",1.5;
"mat",0;
"sat",2;
"cat",2;
"cat",3;
"sat",2}

In B2 enter & copy down:

=SUMPRODUCT(($F$2:$F$8=A2)*($G$2:$G$8>0))

This will give you a count of the co-occurrences of an item with associated non-zero values.

Aladin