Count unique distinct values

Question

I would like to count how many unique distinct scopes a certain country has in the table. The country may appear more than once in the table, in this case it has a different id (row B=”note”). The country may have more than one scope.

In the example below there should be a number 4, because France with id 483, 484, 485 and 486 has scope 9001 (i.e. 4 times).

I have tried to use the following formula, but I think I am stuck…

=SUM(IF(("France"=Findings!E2098:E5000)*("9001"=Findings!H2098:H5000), 1/COUNTIFS(Findings!E2098:E5000, "France", Findings!B2098:B5000, Findings!B2098:B5000, Findings!H2098:H5000, "9001")), 0)

Answer 1

Here is another way of counting using FREQUENCY:

Formula in F1:

=SUM(--(FREQUENCY(IF(D2:D15=9001,IF(C2:C15="France",MATCH(A2:A15,A2:A15,0))),ROW(A2:A15)-ROW(A2)+1)>0))

Enter through CtrlShiftEnter

Answer 2

As per below screenshot please try below formula. You have to adjust ranges for your data setup.

=SUMPRODUCT(($C$2:$C$10="France")*($D$2:$D$10=9001)*(1/COUNTIFS($C$2:$C$10,$C$2:$C$10,$A$2:$A$10,$A$2:$A$10,$D$2:$D$10,$D$2:$D$10)))

If you have Excel365 then you can simplify using-

=COUNTA(UNIQUE(FILTER(A2:A10,(C2:C10="France")*(D2:D10=9001))))