Excel 2007 : Constructing a formula that counts distinct words in a single cell

Originally Posted by NBVC

Try:

=COUNT(FIND(ROW(INDIRECT({"1:32"})),Jan!G2))

Hold the CTRL and SHIFT keys down and then hit ENTER

That won't work here because it won't differentiate between a single 1 and a 1 in 11, 21 or 31, so for this data

6, 17, 25, 32

you'll get a count of 9 because it will count 1, 2, 3, 5, 6, 7, 17, 25 and 32

Assuming only numbers between 1 and 32 separated by comma+space with no extra characters at the start or end you could use this version

=COUNT(FIND(" "&ROW(INDIRECT("1:32"))&","," "&Jan!G2&","))

confirmed with CTRL+SHIFT+ENTER

or to avoid CSE

=SUMPRODUCT(ISNUMBER(FIND(" "&ROW(INDIRECT("1:32"))&","," "&Jan!G2&","))+0)