Hi Johan,

Am Wed, 6 May 2020 11:18:35 -0700 (PDT) schrieb JS SL:

Column.A exist of several location names.
Column.B exist of names which are related tot the location names in A.
Columnm.C exist of WeekNr's

In Column.D I need to count how many unique values in Column.A are related to the person in column.B in the same week in Column.C

Column.A Column.B Column.C Column.D
aa piet 3 3
aa piet 3 3
bb piet 3 3
cc piet 3 3

dd piet 4 1

dd kees 4 2
ee kees 4 2

ee klaas 4 1
ee klaas 4 1

ee klaas 5 2
ff klaas 5 2

try in D2:

=SUM(1/COUNTIF(OFFSET($A$2,MATCH(B2&C2,$B$2:$B$100&$C$2:$ C$100,0)-1,,COUNTIFS($B$2:$B$20,B2,$C$2:$C$20,C2)),OFFSET($ A$2,MATCH(B2&C2,$B$2:$B$100&$C$2:$C$100,0)-1,,COUNTIFS($B$2:$B$20,B2,$C$2:$C$20,C2))))


Regards
Claus B.
--
Windows10
Office 2016