Formula for count of distinct values, multiple conditions, one of which = or <> all repeating values

Question

Excel formula (I know this may work with a pivot table, but wanting a formula) to count distinct values. If this is my table in Excel:

Region | Name   | Criteria 
------ | ------ | ------   
1      | Jill   | A     
1      | Jill   | A
1      | John   | B     
1      | John   | A
2      | Jane   | B     
2      | Jane   | B
2      | Bill   | A     
2      | Bill   | B
3      | Mary   | B     
3      | Mary   | B 
3      | Gary   | A
3      | Gary   | A

In this example, I have the following formual to calculate the distinct values within each region =SUM(--(FREQUENCY(IF((Table1[Region]=A2)*(Table1[Name]<>""),MATCH(Table1[Name],Table1[Name],0)),ROW(Table1[Name])-ROW(Table!B2)+1)>0)) which results in 2 each (Region 1=Jill & John; 2=Jane & Bill, 3=Mary & Gary, each distinct name counted once).

I have an addition formula to calculate how many distinct values with criteria where there is at least 1 "B" for each distinct name within each region, by adding *(Table1[Category]="B") after <>"") ... in this example, it would return Region 1=1, Region 2=2, 3=1, because Jill nor Gary do not have "B" - all others have at least one "B".

Now I'm getting stuck on my last formula, where I want to count how many distinct values within each Region have ALL B's in all their occurrences. The outcome should be Region 1=0 (Jill has no B's and John has a B, but also has an A), Region 2=1 (Jane appears twice, counts as 1 distinct value, and both occurrences are B, Bill has a B in one of his), and 3=1 (Mary has all Bs).

Answer 1

It's too complex for a formula-only task, but feasible.

The following array formula does the job. Although you did not specify it, but I suppose that if "Mary" has an A in another region, this should not cancel her counting in region 3, so long as all records with name "Mary" in region 3 have a "B". In other words, names can repeat in different regions but should not interfere across regions (which made the formula even longer. I added a test case for this, Mary in region 4 with an A did not interfere with Mary in region 3).

=SUM(IF((Table1[Region]=Table1[@Region])*(0=COUNTIFS(Table1[Region],Table1[@Region],
Table1[Name],Table1[Name],Table1[Criteria],"<>B")), 1/COUNTIFS(Table1[Name],Table1[Name],
Table1[Criteria],"B",Table1[Region],Table1[@Region]), 0))

Enter it then press CtrlShiftEnter. then copy/paste down the column.