Complex Unique Count

[thedeadzeds]

Hi Guys,

Is there a way to do the following:

Count the unique Ref, Name and Date so the required results would show in the 'unique count' column. So the below is basically saying for example, Jim appears 5 times in the table with the same ref no but is on two different days, so essentially it is counting 1 for 01/06/19 and 1 for 02/06/19 but leaving the others blank as essentially they are duplicates. That wasy i can then use the Unique count column to sum the actual unique instances.

I hope this makes sense


[TABLE="class: grid, width: 738"]
<colgroup><col span="6"><col><col><col><col></colgroup><tbody>[TR]
[TD]dealer[/TD]
[TD]ServCode[/TD]
[TD]Tran1[/TD]
[TD]Tran2[/TD]
[TD]Tran3[/TD]
[TD]OpNum[/TD]
[TD]RefNo[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Unique Count[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]MOT[/TD]
[TD]IBB[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]527[/TD]
[TD="align: right"]1[/TD]
[TD]Dave[/TD]
[TD="align: right"]01/06/19[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]NSK[/TD]
[TD]BPOLLEN[/TD]
[TD]IBB[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]292[/TD]
[TD="align: right"]2[/TD]
[TD]Beth[/TD]
[TD="align: right"]01/06/19[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]NSK[/TD]
[TD]SER[/TD]
[TD]ENQ[/TD]
[TD]QCH[/TD]
[TD] [/TD]
[TD="align: right"]292[/TD]
[TD="align: right"]2[/TD]
[TD]Beth[/TD]
[TD="align: right"]01/06/19[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]SER[/TD]
[TD]ENQ[/TD]
[TD]COD[/TD]
[TD] [/TD]
[TD="align: right"]292[/TD]
[TD="align: right"]2[/TD]
[TD]Beth[/TD]
[TD="align: right"]01/06/19[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]MCH[/TD]
[TD]MEC[/TD]
[TD]CAN[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]292[/TD]
[TD="align: right"]3[/TD]
[TD]Jim[/TD]
[TD="align: right"]01/06/19[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]MCH[/TD]
[TD]MEC[/TD]
[TD]CAN[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]292[/TD]
[TD="align: right"]3[/TD]
[TD]Jim[/TD]
[TD="align: right"]01/06/19[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]MCH[/TD]
[TD]MEC[/TD]
[TD]CAN[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]292[/TD]
[TD="align: right"]3[/TD]
[TD]Jim[/TD]
[TD="align: right"]01/06/19[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]MCH[/TD]
[TD]MEC[/TD]
[TD]CAN[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]293[/TD]
[TD="align: right"]3[/TD]
[TD]Jim[/TD]
[TD="align: right"]02/06/19[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]MCH[/TD]
[TD]MEC[/TD]
[TD]CAN[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]294[/TD]
[TD="align: right"]3[/TD]
[TD]Jim[/TD]
[TD="align: right"]02/06/19[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NSK[/TD]
[TD]MEC[/TD]
[TD]W3B[/TD]
[TD]QB[/TD]
[TD] [/TD]
[TD="align: right"]292[/TD]
[TD="align: right"]4[/TD]
[TD]Jim[/TD]
[TD="align: right"]01/06/19[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[tyija1995]

Hey,

I think this should work:

IF(SUMPRODUCT(($G$2:G2=G2)*($H$2:H2=H2)*($I$2:I2=I2))=1,1,"")

Assuming the Unique Count column is Col J and the first row used is the 2nd row.

 

[Fluff]

Another option
=IF(COUNTIFS(H$2:H2,H2,G$2:G2,G2,I$2:I2,I2)=1,1,"")

 

[Marcelo Branco]

Is possible to achieve the desired result, 5 in this case, without Unique Count column.

Array formula
=SUM(IF(FREQUENCY(MATCH(G2:G11&"|"&I2:I11,G2:G11&"|"&I2:I11,0),ROW(G2:G11)-ROW(G2)+1),1))
Ctrl+Shift+Enter

M.

 

[thedeadzeds]

Thank you everyone this is amazing.

 

[tyija1995]

No problem, glad that we could help!

 

[Fluff]

You're welcome