excelformulacountif

Excel formula to count duplicates as one

I am trying to create a formula which will count all rows (duplicates as one) based on a value in another column.

Column A Column B.
p1 Joe Bloggs
p2 Bob Bloggs
p1 Joe Bloggs
p3 John Bloggs
p1 Mary Bloggs
p1 Alan Bloggs

I want to count the number of rows where the value in column A === p1. So on the above running

=COUNTIF(A1:A20,"p1") will return 4, but as Joe Bloggs is p1 twice I only want to count Joe Bloggs once.

Running =SUMPRODUCT((B1:B15<>"")/COUNTIF(B1:B15,B1:B15&"")) will count duplicates as one, this would return 5 people as it would only count Joe Bloggs once.

Is it possible to combine the COUNTIF and SUMPRODUCT.

Something else I tried was adding this forumla in Column C, =IF(COUNTIFS(A$1:A3,A3,B$1:B3,B3)>1, COUNTIFS(A$1:A3,A3,B$1:B3,B3)-1,"") this would count the duplicates and then I can run =COUNTIF(Sheet1!A1:A20,"p1")-COUNTIF(Sheet1!C1:C7,">0") but if p2 or p3 had duplicates then this would not generate the correct result.

So basically I am trying to say, IF Column A is p1 then count all p1 rows but IF column B has duplicates then only count the first instance (counting the duplicates as 1)

Solution

  • You can use array formula:

    =SUM(--(FREQUENCY(IF(A2:A10=E2,MATCH(B2:B10,B2:B10,0)),ROW(B2:B10)-1)>0))

    Array formula after editing is confirmed by pressing ctrl + shift + enter

    enter image description here