How to get excel to find the numbers of times 2 numbers appear

[Gary Hunt]

Can someone please tell me how to get excel to count how
many times 2 numbers appear in a series of sets of 7
numbers?

I am try to write a lottery database programme, to
calculate the number of times each pair of numbers has
appeared in all the previous lottery draws. (i.e. 1,2 -
1,3 - 1,4 etc all the way up to 48,49.

I can get excel to count how many times 1 number has come
up using the 'countif' function. see below

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

but i cannot get it to work with 2 numbers, i.e 1+2

I have tried loads of permitations of the above countif,
and count, but I cannot seem to get it right.


PLEASE HELP ME I'M GOING MAD!!!!!

I can e-mail you spreadsheet if that will help

Thanks again, and for taking the trouble to read through
this

Gary Hunt

 

[Bob Phillips]

Hi Gary,

Is this what you want

=SUMPRODUCT((ISNUMBER(FIND(1,A1:A10)))*(ISNUMBER(FIND(2,A1:A10))))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Michael]

Hi Gary

But you can get full statictical information from
www.national-lottery.co.uk.