Thread: Complex Unique Count Thanks:  3 Post #5314987 (1)Post #5314996 (1)Post #5314999 (1) Likes: 0

1. Complex Unique Count

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

 dealer ServCode Tran1 Tran2 Tran3 OpNum RefNo Name Date Unique Count CA MOT IBB 527 1 Dave 01/06/19 1 NSK BPOLLEN IBB 292 2 Beth 01/06/19 1 NSK SER ENQ QCH 292 2 Beth 01/06/19 CA SER ENQ COD 292 2 Beth 01/06/19 MCH MEC CAN 292 3 Jim 01/06/19 1 MCH MEC CAN 292 3 Jim 01/06/19 MCH MEC CAN 292 3 Jim 01/06/19 MCH MEC CAN 293 3 Jim 02/06/19 1 MCH MEC CAN 294 3 Jim 02/06/19 NSK MEC W3B QB 292 4 Jim 01/06/19 1

2. Re: Complex Unique Count

Hey,

I think this should work:
Code:
`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.

3. Re: Complex Unique Count

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

4. Re: Complex Unique Count

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.

5. Re: Complex Unique Count

Thank you everyone this is amazing.

6. Re: Complex Unique Count

No problem, glad that we could help!

7. Re: Complex Unique Count

You're welcome