30

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

Improve this question
2
  • Currently, if I do this calculation in the footer row, or another table, they both show up as "0"
    – Greg K.
    Commented Jul 5, 2012 at 23:05
  • Shows up as "0" because I'm not sure how to use the Count function really well.
    – Greg K.
    Commented Jul 7, 2012 at 23:28

4 Answers 4

Reset to default
19

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.

Improve this answer
1
  • 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
    Commented May 31, 2017 at 14:04
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.

Improve this answer
7
  • 4
    Copy the column and run pbpaste| sort | uniq -c in a terminal.
    – vrypan
    Commented Feb 2, 2015 at 8:24
  • why do you need sort?
    – CousinCocaine
    Commented Apr 13, 2015 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
    Commented Nov 20, 2015 at 4:24
  • 2
    You can also use sort -u instead of sort | uniq
    – damiankloip
    Commented Feb 23, 2016 at 15:43
  • This one is always better than clunky gui, thanks for the reminder bro!
    – valignatev
    Commented Jun 1, 2017 at 21:09
9

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

To get count of unique value:

  1. Make sure your table is selected
  2. On toolbar, click Pivot Table
  3. In sidebar, select your column under Fields
  4. 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

Improve this answer
2
  • 2
    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
    Commented Mar 31, 2023 at 12:41
  • Agreed, this (using a Pivot table) is the best solution as it is the most up to date one within the Numbers app. Silly Numbers doesn't have =COUNTUNIQUE as an option still after so many years. But, the pivot table works! Thank you @Paul for your contribution. Upvote for you.
    – CWarrington
    Commented Jul 31 at 4:27
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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .