SUMPRODUCT, SUM(IF(...), or some combination?

[roberthofheimer]

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?

 

[JMB]

Assuming your table is in A1:H6 and cell B10 = "Low" (you can hardcode "Low"
into the formula if you want), and

B2:B5 - Ranks
D2:H5 - Individual indicators for the colors.

=SUM(--(FREQUENCY(IF(($B$2:$B$5=B10)*($D$2:$H$5<>""),COLUMN($D$2:$H$5),""),
IF(($B$2:$B$5=B10)*($D$2:$H$5<>""),COLUMN($D$2:$H$5),""))>0))

array entered with Cntrl+Shift+Enter (or you'll get #VALUE!). There may be
a simpler way, but it's getting late for me.

 

[RobertH]

Works great! Thanks for your help. I've ended up adding a few more
booleans and it continues to function just fine.

great solution!

-Robert