R

#### RobertH

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?