Count unique values in a column with blanks for each row in excel

0
votes

I have a column of data (shown below) that I want to count each unique value in this list. I used this formula: SUMPRODUCT((B2:B11540<>"")/COUNTIF(B2:B11540,B2:B11540&""))

However it gives me the entire count of unique ids and I want to count per row. Also, since this column has lot's of blank fields I am not able to count for each row using CountIf. Ideally the blank rows should give 0 and other duplicates should be counted as 1. Does anyone has a way of solving this?

A-2019-000084

A-2019-000141

A-2019-002944

A-2019-000222

A-2019-000222

A-2019-000222

A-2019-000222

A-2019-004606

A-2019-004606


A-2019-000923

A-2019-000699
2
excelexcel-formula
so what should be the answer with the data you gave? - Scott Craner
So the answer should be each unique element is counted once and blank rows are not counted. For my data the corresponding rows should have 1,1,1,0,0,0,1(A-2019-000222- counting this once),1,0 (this is for that blank after the second A-2019-004606) I need this per row, not the whole total at once - Karan Kashyap

2 Answers

0
votes

use COUNTIF with a variable range:

=--AND(A1<>"",COUNTIF($A$1:A1,A1)=1)

enter image description here

0
votes

For a total unique non-blank count of your sample data in column A use,

=SUMPRODUCT((A2:A23<>"")/(COUNTIFS(A2:A23,A2:A23,A2:A23,"<>")+NOT(SIGN(LEN(A2:A23)))))

For a row-by-row count, (with the first A-2019-000084 in A2) use this in B2 and drag down.

=--(COUNTIFS(A$2:A2, A2, A$2:A2,"<>")=1)