How to count a unique value that appears more than once with one criterion

Question

I am trying to create a formula in excel that will count unique values that appear more than once based on one criterion.

Column A	Column B	Column C
Facility	MRN	Risk Score
Facility 1	456213	High Risk
Facility 2	150004	Moderate Risk
Facility 3	158182	Moderate Risk Rising
Facility 3	158182	Moderate Risk Rising
Facility 4	180005	High Risk Rising
Facility 4	180005	High Risk Rising
Facility 4	180005	High Risk Rising
Facility 4	180005	High Risk Rising
Facility 1	225100	Moderate Risk
Facility 2	235361	Low Risk Rising
Facility 2	235361	Low Risk Rising
Facility 2	235361	Low Risk Rising

The formula I tried is: =SUM(--(LEN(UNIQUE(FILTER($B:$B,($A:$A=$B7)*($C:$C=G$5),"")))>0))

The issue I am having is calculating ONLY the MRNs for each facility that appear more than once. In the above formula $B7 represents the facility name and G$5 represents Risk Score name cell location on a different worksheet.

Facility	Risk Score	Total ##	Risk Score	Total ##
Facility 1	Low Risk	Total ##	Moderate Risk	Total ##
Facility 2	Low Risk	Total ##	Moderate Risk	Total ##

Answer 1

I'm not sure if I understood the question correctly, but this creates a filter of the Columns A to C where the count of the Risk Score for each Facility is equal to 1:

=LET(data;A1:C13;
                 a;INDEX(data;;1);
                 c;INDEX(data;;3);
        unique;UNIQUE(data);
                 ua;INDEX(unique;;1);
                 uc;INDEX(unique;;3);
        riskcount;COUNTIFS(a;ua;c;uc);
        FILTER(unique;riskcount=1))

This results in the following list (green):

I showed the unique values of columns A:C and it's count of risk scores to explain what values are returned (only the values with count of 1).

You might want to wrap the FILTER in SORT to get a sorted result

In case you don't have Office 365 you might want to use the following: =IFERROR(INDEX($A$2:$C$13,AGGREGATE(15,6,1/(NOT(COUNTIFS($A$2:$A$13,$A$2:$A$13,$C$2:$C$13,$C$2:$C$13)>1))*(ROW($A$2:$A$13)-ROW($A$1)),ROW($A1)),COLUMN(A$1)),"")

Confirm with ctrl+shift+enter and copy down & to the right.