Count if number matches critera

Column A contains a a number for each row

Column B to G contains a list of qualifier for each row (different for every row)

What is a good formula to count the total number of occurance where the number in column A for each row matches with one of the qualifier in column B to G of each row.

For example:

3 {1,2,3,4,5,6} COUNT
10 {2,3,5,7,8,9} DO NOT COUNT
22 {11,13,14,15,16,17} DO NOT COUNT
56 {22,23,24,27,28,56} COUNT
-----------------------------------
Total 2

Any ideas? Having a dummy column is not an option...=(

Try this user defined function taking your range of data of interest as its single arguement

Please Login or Register  to view this content.

Paste this into a new module in the VBA editor (alt F11)