How can you create a win-loss fraction across multiple rows and columns?

[ZTR]

Hey Team:

I'm working on a large political database, and am trying to create a win-loss fraction for each race, based on how many counties each candidate or issue won. Ideally, the cell would result in a percentage or a count of total counties won.

For example, in the attached sample I want to create a formula in G11 that counts how many counties the G5 candidate won from ranges G14:G69, compared to the two other candidates in the race in ranges H14:H69 and I14:I69.

Any help is much appreciated!!

 

[bosco_yip]

An interesting question.

Try,

In G11, formula copy across to I11 :

=SUMPRODUCT((RANK($G14:$I69,OFFSET($G14:$I14,ROW($G14:$I69)-ROW($G14:$I14),,,))=1)*($G5:$I5=G5)^ROW($A1:$A56))&"/56"

Regards
Bosco

 

[ZTR]

Bosco,

H/T.

Regards,

ZTR