Forum Discussion
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.
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.
SergeiBaklan. 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.
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.