Forum Discussion
Counting unique entries and creating a list
Is there a straight forward way to get Excel to count the number of unique items in a list and then total how many of each item there are? For example in the list: 4,5,6,7,4,4,5,7 I would like Excel to tell me that there are three 4's, two 5's, one 6, and two 7's.
I'm hoping to use this to create a relatively simple inventory counter without having to manually count each item. I am quite competant with Excel and am in the process of learning how to use Macros and VBA if they can be of any use.
There are several ways to achieve that.
One easy way is, you can insert a Pivot Table and drag the same field you are trying to count the unique occurrences in both the Rows area and the Values area and change the value field setting to Count if the column you are working contains the numeric data.
19 Replies
Yes, Pivot Table is the easiest in pre-DA Excel. When Dynamic Arrays appear that could be
=UNIQUE(A2:A9) and =COUNTIF(A2:A9,UNIQUE(A2:A9))SergeiBaklan I just ran across the UNIQUE function and it does exactly what I need, but it seems to only return the first value in my list. For example your "=UNIQUE(A2:A9)" is returning only "4" in the target cell. Ideally, I wanted to do something like "=UNIQUE(A2:C9)", but that is doing exactly the same and returning only the value of A2 in the formula cell. No dynamic array as expected.
=UNIQUE(A2:A9) shall return the spill with all unique items, please check the file attached. If not, could you please provide the sample.
There are several ways to achieve that.
One easy way is, you can insert a Pivot Table and drag the same field you are trying to count the unique occurrences in both the Rows area and the Values area and change the value field setting to Count if the column you are working contains the numeric data.
Subodh_Tiwari_sktneer The pivot table worked exactly like I was hoping. Thanks!
You're welcome! Glad you found it helpful.
