Counting number of occurrences in column?

Question

What would be a good approach to calculate the number of occurrences in a spreadsheet column? Can this be done with a single array formula?

Example (column A is input, columns B and C are to be auto-generated):

|   A   |   B   |   C   | +-------+-------+-------+ | Name  | Name  | Count | +-------+-------+-------+ | Joe   | Joe   |     2 | | Lisa  | Lisa  |     3 | | Jenny | Jenny |     2 | | Lisa  |       |       | | Lisa  |       |       | | Joe   |       |       | | Jenny |       |       | 

Answer 1

A simpler approach to this

At the beginning of column B, type

=UNIQUE(A:A) 

Then in column C, use

=COUNTIF(A:A, B1) 

and copy them in all row column C.

Edit: If that doesn't work for you, try using semicolon instead of comma:

=COUNTIF(A:A; B1) 

Answer 2

Try:

=ArrayFormula(QUERY(A:A&{"",""};"select Col1, count(Col2) where Col1 != '' group by Col1 label count(Col2) 'Count'";1))

---

22/07/2014 Some time in the last month, Sheets has started supporting more flexible concatenation of arrays, using an embedded array. So the solution may be shortened slightly to:

=QUERY({A:A,A:A},"select Col1, count(Col2) where Col1 != '' group by Col1 label count(Col2) 'Count'",1)