Formulas to sum distinct values with condition "any" and "all"

See attached.

I have a table with location, name and condition (category) fields. On the next sheet, I have a working formula to calculate the number of distinct names (don't count repeated names within the same location) for each location in column B.

What I need next is a formula to calculate the same (number of distinct names), but only count IF at least 1 of the corresponding names also has category 'B' - the group of names may have more than one 'B', but is only counted once. I think I have that formula correct in column C (gray highlight), but you can double check (thank you). Finally, and this is where I know my formula is incorrect (light blue highlight), a distinct name count where ALL the names are category 'A' (no 'Bs' or any other category - my sample only has 2 categories, but my actual data set has a few more categories).

Sample C.xlsx

EDIT - posted in correct forum with correct attachment.

=SUM(--(IFERROR(MATCH(Table1[Name],IF(Table1[Location]=$A3,Table1[Name]),),)=ROW(Table1[Location])-1)) for BB
=SUM(--(IFERROR(MATCH(Table1[Name],IF((Table1[Location]=$A3)*(Table1[Category]=C$2),Table1[Name]),),)=ROW(Table1[Location])-1)) for BB CC
in C2: B, D2: A

Try this array formula in cell D2 and drag down.

Formula:

Please Login or Register  to view this content.

B2 --> =COUNT(MATCH(ROW(Table1[Name])-1,MATCH(Table1[Name],Table1[Name],0)*(Table1[Location]=A2),0))
C2 --> =COUNT(MATCH(ROW(Table1[Category])-1,MATCH(Table1[Name],Table1[Name],0)*(Table1[Location]=A2)*(Table1[Category]="B"),0))
D2 --> =COUNT(MATCH(ROW(Table1[Category])-1,MATCH(Table1[Name],Table1[Name],0)*(Table1[Location]=A2)*(Table1[Category]="A"),0))

It's all array formulas.

The structure is the same.

=COUNT(MATCH(ROW(RANGE),MATCH(DISTINCT_RANGE,DISTINCT_RANGE,0)*(CriteriaRange1=Criteria1)*[CriteriaRange2=Criteria2]),0))

Otherwise...

B2 -->=SUM(IF(Table1[Location]=Formulas!A2,1/COUNTIFS(Table1[Location],Formulas!A2,Table1[Name],Table1[Name])))
C2 --> =SUM(IF((Table1[Location]=Formulas!A2)*(Table1[Category]="B"),1/COUNTIFS(Table1[Location],Formulas!A2,Table1[Category],"B",Table1[Name],Table1[Name])))

* array formulas.

https://exceljet.net/formula/count-u...e-with-countif