• 590K Members
• 4,686 Online
• 716K Conversations
SOLVED

Highlighted
New Contributor

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

19 Replies
Highlighted
Solution

# Re: Counting unique entries and creating a list

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.

Highlighted

# Re: Counting unique entries and creating a list

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

# Re: Counting unique entries and creating a list

@Sergei Baklan 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.

Highlighted

# Re: Counting unique entries and creating a list

=UNIQUE(A2:A9) shall return the spill with all unique items, please check the file attached. If not, could you please provide the sample.

Highlighted

# Re: Counting unique entries and creating a list

@Subodh_Tiwari_sktneer  The pivot table worked exactly like I was hoping. Thanks!

Highlighted

Highlighted

# Re: Counting unique entries and creating a list

If I double click the file on the community site, it opens in Google Docs and that same cell is rendered as "=ARRAY_CONSTRAIN(ARRAYFORMULA(UNIQUE(A2:A9)), 4, 1)".

I can't make sense of it, so I'll attach the spreadsheet with real data where I am trying to list unique rows. The failing formula "=UNIQUE(M2:O510)" is in cell U2.

Highlighted

# Re: Counting unique entries and creating a list

xlfn means your version of Excel doesn't support dynamic arrays, in particular UNIQUE function. So far it's available for Office 365 subscribers on Insiders channels.

https://support.office.com/en-us/article/issue-an-xlfn-prefix-is-displayed-in-front-of-a-formula-882...

Highlighted

# Re: Counting unique entries and creating a list

@Sergei Baklan . I have Office 365, but I re-installed it just to make sure. Excel also accepts the formula "=unique(A1:A9)" so it knows about, but it just places what looks like the A1 value into the formula cell, and no other cells are populated.

I have no idea why this behaviour is occuring. If I search in Help for "unique" I get link to use Data |  Sort/Filter | Advanced | Copy to ("B1") Unique records only. If I have in A1:A9

a

a

b

c

b

a

d

d

d

and I get in B1:B5

a

a

b

c

d

Even this is not correct.

Highlighted

# Re: Counting unique entries and creating a list

Check all the 'a' values in the range used in the formula. One of 'a' must be having a leading or a trailing space in it and that's why it's picking two 'a' values.

In C1, place the formula =LEN(A1) and copy it down and check the length returned for all the 'a' values and you will find that the length of all the 'a' values is not 1.

Or you should try this formula which should return the correct output ignoring any leading or trailing spaces.

=UNIQUE(TRIM(A1:A9))

Highlighted

# Re: Counting unique entries and creating a list

If you have UNIQUE from Excel with Dynamic Arrays, Help shall show something like

What you speak about that's Advanced filter. It consider first cell in the list as column label. In your case that's A1. Thus first "a" is considered as list name, not as data. More correct will be like

And word exactly is your Excel channel and build? You may find info in File->Account

Highlighted

# Re: Counting unique entries and creating a list

@Subodh_Tiwari_sktneer Thanks, but if I use "=UNIQUE(TRIM(A1:A9))" I get "#value!"

Highlighted

# Re: Counting unique entries and creating a list

@Sergei Baklan . Thanks Sergei. Still no further in identifying the problem. See attached. "=UNIQUE" is recognized but I am only getting a single entry in the formula cell. I can't work out why it is not working.

Highlighted

# Re: Counting unique entries and creating a list

This is how it works.

Please find the attached with the Unique/Trim formula in C2.

Highlighted

# Re: Counting unique entries and creating a list

I also think that you should have started your own question as it would be irritating for the OP as he/she might be getting all the notifications of replies being posted here and specially when the question has been marked as Solved.

Highlighted

# Re: Counting unique entries and creating a list

Thanks @Subodh_Tiwari_sktneer . I downloaded the spreadsheet and opened it in Excel and, as previously from Sergei, the formula cell B2 contains "=_xlfn.UNIQUE(A2:A10)". If I click on B2, "{=_xlfn.UNIQUE(A2:A10)}" is actually displayed in the formula bar, but if I click there, the braces { and } dissapear.

Highlighted

# Re: Counting unique entries and creating a list

That simply means you don't have access to the dynamic array functions yet.

Join the Office Insider program from your Account page and keep on updating the Office until you have access to the dynamic formulas.

Highlighted

# Re: Counting unique entries and creating a list

Yes, I agree with @Subodh_Tiwari_sktneer , Dynamic Arrays and all related functions, UNIQUE in particular, are not available for your version. To my knowledge they are deployed only for Insiders Fast and Monthly Targeted channel, not for Monthly channel.

Highlighted

# Re: Counting unique entries and creating a list

Thanks @Sergei Baklan and @Subodh_Tiwari_sktneer for persevering with this issue. It is unfortunate that the version of Excel I have doesn't complain about the "=UNIQUE()" formula. THat at least would provide a clear indication of the issue.