Excel formula to count unique text values in a column not working as expected-CodePudding

UPDATE: I just realized that this is only counting UNIQUE entries as in there is only 1, whereas I am looking to count all UNIQUE entries whether it be 1 or duplicated.

The term is DISTINCT that I needed.

What is incorrect about the formula for counting unique text values?

All examples I have found state this is how to calculate the total unique entries in a spreadsheet.

In this example you can see it is NOT calculating correctly for the formula.

=SUM(IF(ISTEXT(A1:A8)*COUNTIF(A1:A8,A1:A8)=1,1,0))

Yes, I am pressing CTRL SHIFT ENTER

CodePudding user response：

Why don't you use the UNIQUE() function for that?
This should work:

=COUNTA(UNIQUE(A1:A8))

If you don't have the UNIQUE() function, you might opt for this:

=SUM(1/COUNTIF(A$1:A$8,A1:A8))

It's in fact quite silly: when you have a letter once, you get 1. When you have it twice, you get 2, ..., by dividing one by that number and adding all of them, you get the number you're looking for.

CodePudding user response：

For Excel 2016:

=SUM(IF(FREQUENCY(MATCH(A1:A8,A1:A8,0),ROW(A1:A8)-MIN(ROW(A1:A8)) 1),1))

If you need the ISTEXT condition:

=SUM(IF(FREQUENCY(IF(ISTEXT(A1:A8),MATCH(A1:A8,A1:A8,0)),ROW(A1:A8)-MIN(ROW(A1:A8)) 1),1))

CodePudding user response：

You can use the following: =SUM(1/(COUNTIF(A1:A8,A1:A8)) to get the count of uniques.

Or if the range includes blanks: =SUM(IFERROR(1/COUNTIF(A1:A9,A1:A9),0))

Older Excel versions require both to be entered with ctrl shift enter

Or I think this doesn't require ctrl shift enter:

=SUMPRODUCT(N(FREQUENCY(IFERROR(MATCH(A1:A8,A1:A80,0),0),ROW(A1:A8))>0))

In Office 365 the UNIQUE function can be used, for instance:

=LET(range,A1:A8,flt,FILTER(range,range<>"",""),IF(flt="",0,COUNTA(UNIQUE(flt))))