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

Also You can use =ARRAYFORMULA(COUNTIF(A2:A8,"=" & B2:B8)). This is a simple way. — contributorpw, Jul 21 '14 at 13:04

score 113 · Answer 1 · edited Oct 10 '19 at 21:29

113

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)

edited Oct 10 '19 at 21:29

player0

69,261
8
33
67

answered Mar 09 '13 at 11:03

Richard Wong

2,848
4
15
18

1

I just checked it, and indeed this looks like an elegant solution! Still, I keep the answer by @AdamL as accepted for now, since it does everything in one line without the need to copy rows in column C. – feklee Mar 09 '13 at 12:30
I can't get this workig, can you see why? This is a sample spreadsheet https://docs.google.com/spreadsheets/d/1HWdVTQAdWKbsdzO80MtYqwk1rNIGqnfqSk24hjNS3Wk/edit?usp=sharing – bluantinoo May 30 '15 at 18:20
Somehow, in your spreadsheet you need to use semicolon instead of comma, i.e. =COUNTIF(B:B; C2). But I can use comma if I create a new spreadsheet myself. not sure why.. – Richard Wong May 31 '15 at 23:10

AdamL · Accepted Answer · 2014-07-21T22:11:53.850

49

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)

edited Jul 21 '14 at 22:11

answered Sep 02 '12 at 05:21

AdamL

20,619
4
57
52

For me, A is a date. When grouping, that date is converted to an integer for the second column? How can I make sure column two has the same format as the first column (a date)? – calf Apr 11 '14 at 14:05
How would this be changed to do the same for multiple columns? – noisygecko Jul 07 '17 at 16:00

score 41 · Answer 3 · edited Aug 31 '16 at 18:13

41

=COUNTIF(A:A;"lisa")

You can replace the criteria with cell references from Column B

edited Aug 31 '16 at 18:13

Fedir RYKHTIK

9,110
6
53
64

answered Aug 26 '12 at 13:48

Srikanth

846
8
18

1

Read again: Column `B` is *to be auto-generated*. – feklee Aug 26 '12 at 13:59

score 4 · Answer 4 · answered Jan 02 '20 at 01:39

4

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)

answered Jan 02 '20 at 01:39

delimiter

519
2
10

score 1 · Answer 5 · edited Oct 10 '19 at 21:30

1

=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

edited Oct 10 '19 at 21:30

player0

69,261
8
33
67

answered Apr 23 '14 at 23:32

lucinne nava

11
1

Nathan Hanna · Answer 6 · 2016-08-24T08:35:38.520

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

Counting number of occurrences in column?

6 Answers6

Linked