Given the following dummy data (my real data involves hundreds of rows,
hundreds of color columns, and multiple "rank" columns):

    A    B    C    D    E    F    G    H
1        Rank    Colors    red    yellow    blue    green    orange
2    John    High    2    Y        Y
3    Paul    Med    0
4    George    Low    1                Y
5    Ringo    Low    2                Y    Y
6    Total            1    0    1    2    1

I'm trying to come up with a formula (without macros) that will
calculate the number of colors associated with a Low ranking Beatle
(result should be 2 because given the data above, there are two colors
(green and orange) associated with the two Beatles with a rank of
"Low".
Note that there are no colors associated with Paul and no Beatles
associated with Yellow.

So, SUMPRODUCT(($B$2:$B$5="Low")*($C$2:$C$5>0)) appears accurate, but
won't give me the right answer for "High" Beatles because it is
essentially a row count where colors > 0 for a given Rank.
I would expect the following values:

Rank    Colors
High    2
Med    0
Low    2

Any ideas?