Sep 10 2019 06:53 AM
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.
Sep 10 2019 07:25 AM
SolutionThere 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.
Sep 10 2019 10:25 AM
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))
Sep 10 2019 05:54 PM
@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.
Sep 11 2019 04:41 AM
=UNIQUE(A2:A9) shall return the spill with all unique items, please check the file attached. If not, could you please provide the sample.
Sep 11 2019 04:48 AM
@Subodh_Tiwari_sktneer The pivot table worked exactly like I was hoping. Thanks!
Sep 11 2019 05:41 AM
You're welcome! Glad you found it helpful.
Sep 11 2019 04:50 PM
@Sergei Baklan. Strange. The cell C2 in your Excel worksheet that I downloaded with "Save link as ..." is "=_xlfn.UNIQUE(A2:A9)".
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.
Sep 12 2019 03:12 AM
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.
Sep 12 2019 06:15 PM
@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.
Sep 13 2019 02:07 AM
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))
Sep 13 2019 03:19 PM
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
Sep 13 2019 10:41 PM
@Subodh_Tiwari_sktneer Thanks, but if I use "=UNIQUE(TRIM(A1:A9))" I get "#value!"
Sep 13 2019 10:44 PM
@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.
Sep 13 2019 10:57 PM
Sep 13 2019 11:01 PM
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.
Sep 13 2019 11:08 PM
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.
Sep 13 2019 11:57 PM
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.
Sep 14 2019 05:45 AM
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.
Sep 14 2019 05:42 PM
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.
Sep 10 2019 07:25 AM
SolutionThere 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.