Numbers: Count unique items in a column

Question

I'd like to count the occurrence of unique items found in one column of about 120 items. I can't find a way to do this in Numbers.app

Currently, if I do this calculation in the footer row, or another table, they both show up as "0" — Greg K., Jul 05 '12 at 23:05
Shows up as "0" because I'm not sure how to use the Count function really well. — Greg K., Jul 07 '12 at 23:28

score 25 · Answer 1 · answered Jul 06 '12 at 09:37

25

If it doesn't have to be updated dynamically, you might also copy the cells and run something like pbpaste | sort | uniq | wc -l.

answered Jul 06 '12 at 09:37

Lri

105,117

why do you need sort? – CousinCocaine Apr 13 '15 at 17:59
5

@CousinCocaine you need sort because uniq only detects repeated lines immediately following each other, not repeated lines anywhere in the input stream. See uniq man page. – Fabian Tamp Nov 20 '15 at 04:24
2

You can also use sort -u instead of sort | uniq – damiankloip Feb 23 '16 at 15:43
3

Copy the column and run pbpaste| sort | uniq -c in a terminal. – vrypan Feb 02 '15 at 08:24
This one is always better than clunky gui, thanks for the reminder bro! – valignatev Jun 01 '17 at 21:09
This is a clean solution. It's obscene that there's no baked-in function for doing this. – ryantuck Mar 09 '21 at 23:53

score 17 · Accepted Answer · edited Jul 06 '12 at 01:29

17

Here is a clever solution posted in an apple discussions thread.

You basically create another column, and use the formula =IF(COUNTIF(A$1:A1, A2)=0,1,""). This will display a one for the first occurrence of each unique item. You then simply sum the whole column and you get your count.

edited Jul 06 '12 at 01:29

Daniel

34,803

answered Jul 06 '12 at 01:05

kevin9794

4,300

6

This is extremely slow when there's lots of data. If that's the case, you can import your data into Google Spreadsheets and use =COUNTUNIQUE(A:A) – Eugene Kulabuhov May 31 '17 at 14:04

Paul · Answer 3 · 2023-02-13T21:33:39.903

6

You can use Numbers' (new) Pivot Table tool to summarize data

To get count of unique value:

Make sure your table is selected
On toolbar, click Pivot Table
In sidebar, select your column under Fields
In sidebar, under Values, select or change "Summarize by" to Count (Unique). You may need to click the little (i) icon.

You will see the answer in the created pivot table

You can summarize by groups ("facet") by selecting additional Fields

You can add more results to the same table, by dragging Fields as new Values

edited Feb 13 '23 at 21:33

answered Feb 13 '23 at 21:28

Paul

221

1

This is the best solution, I think. Step 4 was a little tricky — what I ended up doing was dragging the green lozenge under Rows over to Values and then I could click the (i) and change to Count (Unique). – Matt Sephton Mar 31 '23 at 12:41

score 0 · Answer 4 · answered Feb 13 '23 at 21:56

0

I just select the column and copy, go to Terminal, type wc(return), paste in the copy, type Ctrl-D, and read the count.

answered Feb 13 '23 at 21:56

WGroleau

4,869
7
44
77

Numbers: Count unique items in a column

4 Answers4