sommeprod avec critére de valeur unique sur colonne C

=NB(1/FREQUENCE(SI((('BASE EMPLOI'!$D$2:$D$5000="GRENOBLE")*('BASE EMPLOI'!$E$2:$E$5000=$A5)*('BASE EMPLOI'!$B$2:$B$5000=B$4));EQUIV('BASE EMPLOI'!$C$2:$C$5000;'BASE EMPLOI'!$C$2:$C$5000;0));LIGNE(INDIRECT("1:"&LIGNES('BASE EMPLOI'!$C$2:$C$5000:'BASE EMPLOI'!$C$2:$C$5000)))))

Private Sub Worksheet_Activate()
Sheets("STATISTIQUES").EnableCalculation = False
End Sub

Private Sub Worksheet_Activate()
ActiveSheet.Calculate
End Sub

Private Sub Bt_Calculs_Click()

With Sheets("STATISTIQUES")
Range("Tb_S_ZoneCalculs").Select
Selection.ClearContents
Range("A5").Select


'Colonne B --- A TRAITER
.Range("B5:B" & .Cells(Rows.Count, 1).End(xlUp).Row).FormulaArray = _
    "=COUNT(1/FREQUENCY(IF(((Tb_B_Zone=""GRENOBLE"")*(Tb_B_Type=RC[-1])*(Tb_B_User=""A TRAITER"")),MATCH(Tb_B_Societe,Tb_B_Societe,0)),ROW(INDIRECT(""1:""&ROWS(Tb_B_Societe:Tb_B_Societe)))))"
'Colonne C --- SEBASTIEN
.Range("C5:C" & .Cells(Rows.Count, 1).End(xlUp).Row).FormulaArray = _
    "=COUNT(1/FREQUENCY(IF(((Tb_B_Zone=""GRENOBLE"")*(Tb_B_Type=RC[-2])*(Tb_B_User=""SEBASTIEN"")),MATCH(Tb_B_Societe,Tb_B_Societe,0)),ROW(INDIRECT(""1:""&ROWS(Tb_B_Societe:Tb_B_Societe)))))"
'Colonne E --- ANNONCE
.Range("E5:E" & .Cells(Rows.Count, 1).End(xlUp).Row).FormulaArray = _
    "=COUNT(1/FREQUENCY(IF(((Tb_B_Zone=""GRENOBLE"")*(Tb_B_Type=RC[-4])*(Tb_B_Annonce=""ANNONCE"")),MATCH(Tb_B_Societe,Tb_B_Societe,0)),ROW(INDIRECT(""1:""&ROWS(Tb_B_Societe:Tb_B_Societe)))))"
'Colonne F --- CANDIDATURE SPONTANEE
.Range("F5:F" & .Cells(Rows.Count, 1).End(xlUp).Row).FormulaArray = _
    "=COUNT(1/FREQUENCY(IF(((Tb_B_Zone=""GRENOBLE"")*(Tb_B_Type=RC[-5])*(Tb_B_Annonce=""CANDIDATURE SPONTANEE"")),MATCH(Tb_B_Societe,Tb_B_Societe,0)),ROW(INDIRECT(""1:""&ROWS(Tb_B_Societe:Tb_B_Societe)))))"
'Colonne H --- A CANDIDATER
.Range("H5:H" & .Cells(Rows.Count, 1).End(xlUp).Row).FormulaArray = _
        "=COUNT(1/FREQUENCY(IF(((Tb_B_Zone=""GRENOBLE"")*(Tb_B_Type=RC[-7])*(Tb_B_CommentairesPostes=""A CANDIDATER"")),MATCH(Tb_B_Societe,Tb_B_Societe,0)),ROW(INDIRECT(""1:""&ROWS(Tb_B_Societe:Tb_B_Societe)))))"
'Colonne I --- A RELANCER
.Range("I5:I" & .Cells(Rows.Count, 1).End(xlUp).Row).FormulaArray = _
        "=COUNT(1/FREQUENCY(IF(((Tb_B_Zone=""GRENOBLE"")*(Tb_B_Type=RC[-8])*(Tb_B_CommentairesPostes=""A RELANCER"")),MATCH(Tb_B_Societe,Tb_B_Societe,0)),ROW(INDIRECT(""1:""&ROWS(Tb_B_Societe:Tb_B_Societe)))))"
'Colonne J --- NE PAS CANDIDATER
.Range("J5:J" & .Cells(Rows.Count, 1).End(xlUp).Row).FormulaArray = _
        "=COUNT(1/FREQUENCY(IF(((Tb_B_Zone=""GRENOBLE"")*(Tb_B_Type=RC[-9])*(Tb_B_CommentairesPostes=""NE PAS CANDIDATER"")),MATCH(Tb_B_Societe,Tb_B_Societe,0)),ROW(INDIRECT(""1:""&ROWS(Tb_B_Societe:Tb_B_Societe)))))"
'Colonne K --- PAS DE RETOUR
.Range("K5:K" & .Cells(Rows.Count, 1).End(xlUp).Row).FormulaArray = _
        "=COUNT(1/FREQUENCY(IF(((Tb_B_Zone=""GRENOBLE"")*(Tb_B_Type=RC[-10])*(Tb_B_CommentairesPostes=""PAS DE RETOUR"")),MATCH(Tb_B_Societe,Tb_B_Societe,0)),ROW(INDIRECT(""1:""&ROWS(Tb_B_Societe:Tb_B_Societe)))))"
'Colonne L --- REPONSE NEGATIVE
.Range("L5:L" & .Cells(Rows.Count, 1).End(xlUp).Row).FormulaArray = _
        "=COUNT(1/FREQUENCY(IF(((Tb_B_Zone=""GRENOBLE"")*(Tb_B_Type=RC[-11])*(Tb_B_CommentairesPostes=""REPONSE NEGATIVE"")),MATCH(Tb_B_Societe,Tb_B_Societe,0)),ROW(INDIRECT(""1:""&ROWS(Tb_B_Societe:Tb_B_Societe)))))"
'Colonne N --- NB RdV
.Range("N5:N" & .Cells(Rows.Count, 1).End(xlUp).Row).FormulaR1C1 = _
        "=SUMPRODUCT(('BASE EMPLOI'!R2C5:R5000C5=STATISTIQUES!RC1)*('BASE EMPLOI'!R2C4:R5000C4=""NB RdV"")*('BASE EMPLOI'!R2C46:R5000C46))"

End With