How to grt excel to count 2 numbers in a range

[Gary Hunt]

I am trying to write a lottery databse spreadsheet, but I
cannot get the last tab to work correctly.

What I want to do is count how many times a pair (2)
Numbers occur in all of the previous lottery draws.

see below

=COUNTIF('Draw Data'!C2:I1001,"1,2")

It works for one number see below

=COUNTIF('Draw Data'!C2:I1001,"1")

But if you ask it to find how many times 2 numbers occur
in a sequence it fails.

I have tried loads of permitations of the above but
nothing works.

I can also e-mail you the entire programme in case you
don't know what I am getting at.

Many thanks


Gary Hunt

 

[Domenic]

I am trying to write a lottery databse spreadsheet, but I
cannot get the last tab to work correctly.

What I want to do is count how many times a pair (2)
Numbers occur in all of the previous lottery draws.

see below

=COUNTIF('Draw Data'!C2:I1001,"1,2")

It works for one number see below

=COUNTIF('Draw Data'!C2:I1001,"1")

But if you ask it to find how many times 2 numbers occur
in a sequence it fails.

I have tried loads of permitations of the above but
nothing works.

I can also e-mail you the entire programme in case you
don't know what I am getting at.

Many thanks


Gary Hunt

Hi Gary,

try,

=COUNTIF('Draw Data'!C2:I1001,"*1,2*")

Hope this helps!

 

[Arvi Laanemets]

Hi

=SUM(IF((A1:A100)=(B1:B100),1,0))
entered as array function counts occurences of doubles on same row in ranges
A1:A100 and B1:B100.

=SUMPRODUCT((COUNTIF(A1:A10,A1:A10)=2)/2)
entered as array function counts double (but only double, not triple or
quadruple or ...) entries in range A1:A100