Count "YES" in B1:B8, C1:C8, etc when corresponding A="DETROIT"

[dave351]

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!!

[Special-K]

=SUMPRODUCT((A1:A8="DETROIT")*(B1:B8="Yes"))
=SUMPRODUCT((A1:A8="DETROIT")*(C1:C8="Yes"))
=SUMPRODUCT((A1:A8="PHOENIX")*(B1:B8="Yes"))
=SUMPRODUCT((A1:A8="PHOENIX")*(C1:C8="Yes"))

[dave351]

Special-K, that works great. Thank you very much for your assistance! First time I've used that particular function!!

Cheers,
Dave