COUNTIF Unique values in adjaceme columns

[Paul Naylor]

Have a worksheet containing 2 columns.

Column A contains Order Numbers e.g. Ord123457 and Column B Names. Column A and B can contain duplicate values. Column C contains a list of all the unique names from column B

Formula on column D

Want a formula is column D , to lookup name in C against the name in B and count ONLY the unique order numbers column A registered against each of the names

ColumnA ColumnB ColumnC Column D
Ord12345 James James 2
Ord12345 James Bill 1
Ord23456 Bill Simon 3
Ord23456 James
Ord34567 Simon
Ord23456 James
Ord34567 Simon
Ord56789 Simon
Ord78912 Simon

 

[Paul Naylor]

Hi Fluff,

I've decided to simply copy and paste the info onto one sheet and run the formula against a single workbook although now realised that I'll need a formula for unique values based on additional Criteria columns C ( time order raiseddd/mm/yyyy hh:mm, such that only display unique value if column A and C are unique

Original formula for 1 unique value column A
=SUM(--(FREQUENCY(IF($B$2:$B$6796=J2,MATCH($A$2:$A$6796,$A$2:$A$6796,0)),ROW($A$2:$A$6796)-ROW($A$2)+1)>0))

Not sure how to adapt to contain 2 unique values ?

 

[Fluff]

How about

=SUM(--(FREQUENCY(IF($B$2:$B$6796=J2,MATCH($A$2:$A$6796&$C$2:$C$6796,$A$2:$A$6796&$C$2:$C$6796,0)),ROW($A$2:$A$6796)-ROW($A$2)+1)>0))

 

[Paul Naylor]

How about

=SUM(--(FREQUENCY(IF($B$2:$B$6796=J2,MATCH($A$2:$A$6796&$C$2:$C$6796,$A$2:$A$6796&$C$2:$C$6796,0)),ROW($A$2:$A$6796)-ROW($A$2)+1)>0))

Hi Fluff, fantastic works a treat. Really appreciate you taking the time to help me out on more than one occasion. Always try to find a solution on the web before posting.

Once again massive thanks for all the help you've given me.

Regards,

Paul

 

[Fluff]

Glad to help & thanks for the feedback.