count combinations

W

WYN

hi all,i have a lagre list of numbers (a1:j1000)i have to locate three digit
number combinations that may be in each row.and how many times this
combination occurs.is there a formula to do this? -- thanks wynb
wynb
 
N

Nick B

You might be able to do something like:
=SUM(ISNUMBER(FIND("123",A1:J1000))*1) as an array where 123 is the number
combination you are looking for.
To enter a formula as an array, copy and paste the code into the cell, but
rather than hitting Enter, hit Ctrl+Shift+Enter.
Now this will only count each cell once. So if I use "123" then if I have
123 in A1 and 123123 in A2, it will only return 2 (once for each cell).

Something like this?
 
W

WYN

hi nick b, thank you,that works perfectly. wynb

Nick B said:
You might be able to do something like:
=SUM(ISNUMBER(FIND("123",A1:J1000))*1) as an array where 123 is the number
combination you are looking for.
To enter a formula as an array, copy and paste the code into the cell, but
rather than hitting Enter, hit Ctrl+Shift+Enter.
Now this will only count each cell once. So if I use "123" then if I have
123 in A1 and 123123 in A2, it will only return 2 (once for each cell).

Something like this?
Click to expand...
 
Top