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?

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

My bad, wasn't sure if it was a "programming" or "worksheet functions"
question. JMB's very helpful response can be found here:
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$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?
> >
> >

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 here:
> 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$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?
> > >
> > >
>
>