Count the number of occurrences of different words

[NJS1982]

Hi all, hopefully a fairly simple one. Does anybody know if there is a formula to count up the number of times individual words appear in a list?

I have a list of projects (2,500 in total), each with a single sport attached (could be tennis, basketball, football or many others). Is there a way to count up the number of these sports, using a single formula, to tell me the total number of differing sports that are in the list (i.e. not count the duplicates)?

I know how to do it with a pivot (there are 70 different sports listed) but can't work out how to do it with a formula. Count something, maybe?

 

[kvsrinivasamurthy]

One example
A2:A12 is the range which contains duplicates
Formula to count individuals

=SUMPRODUCT(1/COUNTIF(A2:A12,A2:A12))

 

[Dobo_Bobo]

One example
A2:A12 is the range which contains duplicates
Formula to count individuals

=SUMPRODUCT(1/COUNTIF(A2:A12,A2:A12))

The formula Kvsrinivasamurthy provided will work, but it's an array formula, thus it will only work if you press Ctrl+Shift+Enter

E.g. {=SUM(1/COUNTIF(A2:A12,A2:A12))}

 

[NJS1982]

Great! Thanks both!

 

[kvsrinivasamurthy]

CSE not required. SUMPRODUCT itself is array formula. Just Enter will work.

 

[Dobo_Bobo]

CSE not required. SUMPRODUCT itself is array formula. Just Enter will work.

Thanks for letting me know, I didn't remember it correctly from when I learnt how to do it.