Home |
Search |
Today's Posts |
#1
|
|||
|
|||
count combinations
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 |
#2
|
|||
|
|||
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? "WYN" wrote: 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 |
#3
|
|||
|
|||
hi nick b, thank you,that works perfectly. wynb
"Nick B" wrote: 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? "WYN" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
count a group of numbers but do not count duplicates | Excel Worksheet Functions | |||
Count number to reach a cumulative value | Excel Worksheet Functions | |||
Count If Formula | Excel Worksheet Functions | |||
count based on two fields - need quickly | Excel Worksheet Functions |