Thanks:  0
Likes:  0

# Thread: Matching data and returning count

1. 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??

2. 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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•