Count Rows and Lookup Cells based on Multiple Criteria Columns

Hi,

I'm a fairly experienced excel user, but I am totally stumped with this one.

I have a large list of numbers in 6 different columns and I am trying to find all the times that exactly two numbers from another list appear in any of the 6 columns on a single row.

For example:

List A
1 2 3 4 5 6
2 3 5 4 6 7
7 8 9 10 11 12
12 2 1 9 10 13

List B
1 2
2 3
4 5

Count(1 and 2) = 2
Count(2 and 3) = 2
Count(4 and 5) = 2

The closest I have come is this:
=SUMPRODUCT(('Raw Data'!C$2:C$1755=Sheet3!B3)*('Raw Data'!D$2:D$1755=Sheet3!C3))

where it will find the number of times that the two numbers appear in the first two of six columns in that order, but i need it to return the number of times that the two numbers appear in any of the six columns in any order.... eventually, i'd even like it to also return an array of the row numbers (or the values of another cell on the same row) where these numbers appear together, but i'd be happy if i can just get the count for now...

please help!

You could use a single cell calculation to do this but it would be inefficient & most likely Volatile

If you have just 6 columns of data I would suggest creating a concatenated & ordered key of those values in one column and subsequently using basic COUNTIF constructs - less elegant but efficient.

Using your example:

Please Login or Register  to view this content.

Your summary calcs are then:

Please Login or Register  to view this content.

You could choose to avoid ordering the key but you would then need to aggregate two COUNTIFs (ie "*:small:*:large:*" + "*:large:*:small:*")