CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulas
feklee
feklee

Reputation: 7695

Counting number of occurrences in column?

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 |       |       |

Upvotes: 48

Views: 121255

Answers (6)

delimiter
delimiter

Reputation: 795

Just adding some extra sorting if needed

=QUERY(A2:A,"select A, count(A) where A is not null group by A order by count(A) DESC label A 'Name', count(A) 'Count'",-1)

enter image description here

Upvotes: 4

lucinne nava
lucinne nava

Reputation: 11

=arrayformula(if(isblank(B2:B),iferror(1/0),mmult(sign(B2:B=TRANSPOSE(A2:A)),A2:A)))

I got this from a good tutorial - can't remember the title - probably about using MMult

Upvotes: 1

Richard Wong
Richard Wong

Reputation: 3578

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)

Upvotes: 120

Srikanth
Srikanth

Reputation: 836

=COUNTIF(A:A;"lisa")

You can replace the criteria with cell references from Column B

Upvotes: 40

Nathan Hanna
Nathan Hanna

Reputation: 4943

Put the following in B3 (credit to @Alexander-Ivanov for the countif condition):

={UNIQUE(A3:A),ARRAYFORMULA(COUNTIF(UNIQUE(A3:A),"=" & UNIQUE(A3:A)))}

Benefits: It only requires editing 1 cell, it includes the name filtered by uniqueness, and it is concise.

Downside: it runs the unique function 3x

To use the unique function only once, split it into 2 cells:

B3: =UNIQUE(A3:A)

C3: =ARRAYFORMULA(COUNTIF(B3:B,"=" & B3:B))

Upvotes: 1

AdamL
AdamL

Reputation: 24599

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)

Upvotes: 48

Related Questions