CountU from an array - SUMPRODUCT, SUM(IF(...)), both?
 

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$50)) 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?

CountU from an array - SUMPRODUCT, SUM(IF(...)), both?
 

Please don't multipost. See response at other post.


"RobertH" wrote:

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$50)) 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?

CountU from an array - SUMPRODUCT, SUM(IF(...)), both?
 

My bad, wasn't sure if it was a "programming" or "worksheet functions"
question. JMB's very helpful response can be found he
http://groups.google.com/group/micro...414066c8?hl=en

JMB wrote:
Please don't multipost. See response at other post.


"RobertH" wrote:

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$50)) 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?

CountU from an array - SUMPRODUCT, SUM(IF(...)), both?
 

Understandable - sometimes it takes a while for a post to show up on the
server. My intent is to direct others to the other post so the discussion
stays in one place.

"RobertH" wrote:

My bad, wasn't sure if it was a "programming" or "worksheet functions"
question. JMB's very helpful response can be found he
http://groups.google.com/group/micro...414066c8?hl=en

JMB wrote:
Please don't multipost. See response at other post.


"RobertH" wrote:

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$50)) 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?