A1: DETROIT B1: YES C1: YES
A2: DETROIT B2: NO C2: YES
A3: PHOENIX B3: YES C3: NO
A4: PHOENIX B4: NO C4: YES
A5: DETROIT B5: YES C5: NO
A6: DETROIT B6: YES C6: YES
A7: DETROIT B7: NO C7: YES
A8: PHOENIX B8: YES C8: NO
Would like to measure:
  1. Number of "YES" in column B when the corresponding A = "DETROIT"
  2. Number of "YES" in column B when the corresponding A = "PHOENIX"
  3. Number of "YES" in column C when the corresponding A = "DETROIT"
  4. Number of "YES" in column C when the corresponding A = "PHOENIX"

So I would like my results to show:
DETROIT (YES) 3 4
PHOENIX (YES) 2 1

I would then concatenate the results into the following:
  • DETROIT had 3/5 (60%) YES in column B
  • DETROIT had 4/5 (80%) YES in column C
  • PHOENIX had 2/3 (66%) YES in column B
  • PHOENIX had 1/3 (33%) YES in column C

My actual table has 12 columns of YES/NO and will probably expand further - so I'm hoping there is a way to calculate the data with a single formula rather than adding "invisible" columns to the side and calculating the results on a cell-by-cell basis.

Thanks for any assistance you can offer!!