Counting Numnber of Cells in a Table that Have the Same Value

Question

Level 1

48 points

Counting Numnber of Cells in a Table that Have the Same Value

Is there a way/formula to do this, other than they way I'm doing it now, which is by using COUNTIF? In a table with 1000 rows for instance, let's say the values in the A column are what I'm keying off. I would like a count of all the cells in the A column that have the word "example" as their value. Right now for each unique value in a cell in the A column I am manually creating a COUNTIF(A1:A1000,") but if there are many unique values in the A column, it's quite laborious. Ideally I'd like to just have a table generated that gives me the top 5 or top 10 most occurring cell values in the A column in the table. What's the best way to do this?

G5 Dual 2GHz, Pismo PowerBook, Mac OS X (10.4.10)

Posted on Sep 27, 2007 11:27 AM

Reply

Answer 1

5-vv

Level 3

685 points

Sep 27, 2007 1:00 PM in response to udog

If I understand your question correctly, you'll find several approaches in this thread:

http://discussions.apple.com/thread.jspa?messageID=5279074

Reply

Answer 2

udog Author

Level 1

48 points

Sep 27, 2007 1:17 PM in response to 5-vv

That's close, but not quite what I was looking for. The solution you pointed me to gives a way to list only the unique items in the list (removes duplicates). I am interested in getting a total number of times each unique item appears in the list. Sorry if I wasn't clear.

Reply

Answer 3

udog Author

Level 1

48 points

Sep 27, 2007 1:20 PM in response to udog

So for instance, for a column like this:
1
1
1
2
2
4
5
I want a way to get back the number of times 1 appears in the list (3), the number of times 2 appears in the list (2), 4 (1), 5 (1), and so on. If I do a COUNTIF and there are thousands of rows, I have to manually put the matching string in each one.

Reply