Count number of rows matching criteria

[cynti]

So I've started to play around with Excel a bit and stumbled on a problem which I haven't managed to find a solution for.
It's a lottery game I'm making statistics of. In this lottery they draw 20 numbers out of 70 possible. I have gathered into a spreadsheet some of the winning numbers (that is I have several rows containing 20 numbers).

Now I want to count the number of rows that contain two specific numbers, say I want to know the number of winning lottery rows that contained BOTH 15 AND 62.
Any ideas how this could be done?

 

[aaronglover]

Are all 20 lottery numbers in the same cell or in different cells spanning a total of 20 columns?

 

[cynti]

Every number is in its own cell, 20 columns.

 

[barry houdini]

Hello cynti, welcome to MrExcel

Lets say your first set of numbers is in A1:T1 and you have 30 sets down to row 30. List your numbers to match somewhere on the worksheet, e.g. 15 in Z2 and 62 in Z3

Then use this formula to count the number of rows that contain both

=SUM(IF(FREQUENCY(IF(COUNTIF(Z2:Z3,A1:T30),ROW(A1:T30)),ROW(A1:A30))=COUNT(Z2:Z3),1))

This is an "array formula" which needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar.

 

[cynti]

Hi barry and thanks for your reply. I just tested your function but didn't manage to make it work. I get errors in excel 2007 and some earlier version we have at school and also in OpenOffice Calc. When I click OK on the error message "Z3,A1" gets highlighted on the function =SUM(IF(FREQUENCY(IF(COUNTIF(Z2:Z3,A1:T30)

 

[barry houdini]

That looks like it's highlighting the first comma. Some versions of Excel need semi-colons ; instead of commas, i.e.

=SUM(IF(FREQUENCY(IF(COUNTIF(Z2:Z3;A1:T30);ROW(A1:T30));ROW(A1:A30))=COUNT(Z2:Z3);1))

 

[cynti]

Oh, thats right! Now it works
Cheers mate! And happy new year