Complex Unique Count

thedeadzeds

Active Member
Joined
Aug 16, 2011
Messages
442
Office Version
  1. 365
Platform
  1. Windows
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


dealerServCodeTran1Tran2Tran3OpNumRefNoNameDateUnique Count
CAMOTIBB 5271Dave01/06/191
NSKBPOLLENIBB 2922Beth01/06/191
NSKSERENQQCH 2922Beth01/06/19
CASERENQCOD 2922Beth01/06/19
MCHMECCAN 2923Jim01/06/191
MCHMECCAN 2923Jim01/06/19
MCHMECCAN 2923Jim01/06/19
MCHMECCAN 2933Jim02/06/191
MCHMECCAN 2943Jim02/06/19
NSKMECW3BQB 2924Jim01/06/191

<colgroup><col span="6"><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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.
 
Upvote 0
Another option
=IF(COUNTIFS(H$2:H2,H2,G$2:G2,G2,I$2:I2,I2)=1,1,"")
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top