Formula to count number of repetitions in a table

[virtualdark]

I need Formula to count number of repetitions in a table
FOr the example, let's say it is LOTTO

1 5 14 40 43
10 13 33 45 48
3 5 14 20 22
20 22 25 48 50
5 7 14 40 41
9 10 13 33 45
2 3 5 23 27
etc


Example Result
How many times 20 and 22 show up in the same line 2
How many times 5 and 14 show up in the same line 3
How many times 10, 13 and 33 show up in the same line 2

I have attached a file Loto.xlsx
Please help! I was using countif and Hlookup but I'm messing it up.

[benishiryo]

hi virtualdark. i suggest you put the individual numbers in the cells instead of putting them in H5. say 20 & 22 are in I5 & J5, then do this array formula in N5:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

i allowed a possibility of 5 numbers. if you want to do it manually without separating the numbers, then it looks like:

Please Login or Register  to view this content.

change those in red. 1st one referring to the number combinations you want. 2nd one is how many numbers are there

[virtualdark]

Thank you... the first one works...
The one with:
=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH($A$1:$E$7,{20,22},0)),ROW($A$1:$E$7)),ROW($A$1:$E$7))=2,1))
doesn't! Do you know why?

[virtualdark]

Haaaa... it didn't work with this example, But I have tried in my file and it works... soooo... it's solved :-D
Thank you Benishiryo :-D