Lookup

[Connie Martin]

I assume I use a lookup function, but not sure. I would define the range
with a name, I know that much, but the formula stumps me. I have a list that
could read something like this:

JAN Con-Way 2
JAN SFS 3
APR SFS 3
JUN SFS 3
JUL TransX 4
JAN Yellow 4
JAN Yellow 4
JAN Yellow 4
JAN Yellow 4
SEP TransX 5
FEB Yellow 5
FEB Yellow 5

In another cell, for example, I would like to put a formula that would look
up all of "Yellow" in this list and then give me the average of the numbers
for Yellow. I think it's simple enough but I'm not versed enough! Connie

[bpeltzer]

SUMIF will total cells when an associated cell meets a certain condition;
COUNTIF will count such cells. So your average would be
=sumif(b:b,"Yellow",c:c)/countif(b:b,"Yellow"). Note that "Yellow" could
just as well be another cell reference.

"Connie Martin" wrote:

> I assume I use a lookup function, but not sure. I would define the range
> with a name, I know that much, but the formula stumps me. I have a list that
> could read something like this:
>
> JAN Con-Way 2
> JAN SFS 3
> APR SFS 3
> JUN SFS 3
> JUL TransX 4
> JAN Yellow 4
> JAN Yellow 4
> JAN Yellow 4
> JAN Yellow 4
> SEP TransX 5
> FEB Yellow 5
> FEB Yellow 5
>
> In another cell, for example, I would like to put a formula that would look
> up all of "Yellow" in this list and then give me the average of the numbers
> for Yellow. I think it's simple enough but I'm not versed enough! Connie

[Connie Martin]

Thank you, this works! Another question, if you don't mind. Using the
sample columns below, I need a formula that looks for Yellow and counts the
number of times it was 7 or less from column C. In looking at the little
table below, the answer would be 9. for SFS it would be 2, for FFE it would
be 1. They will be all separate formulas, of course. I only need one
example---for Yellow.

Yellow 5
Yellow 6
SFS 3
Yellow 6
Yellow 6
Yellow 6
Yellow 6
Yellow 7
Yellow 7
Yellow 7
Yellow 8
FFE 7
FFE 11
FFE 11
SFS 3


"bpeltzer" wrote:

> SUMIF will total cells when an associated cell meets a certain condition;
> COUNTIF will count such cells. So your average would be
> =sumif(b:b,"Yellow",c:c)/countif(b:b,"Yellow"). Note that "Yellow" could
> just as well be another cell reference.
>
> "Connie Martin" wrote:
>
> > I assume I use a lookup function, but not sure. I would define the range
> > with a name, I know that much, but the formula stumps me. I have a list that
> > could read something like this:
> >
> > JAN Con-Way 2
> > JAN SFS 3
> > APR SFS 3
> > JUN SFS 3
> > JUL TransX 4
> > JAN Yellow 4
> > JAN Yellow 4
> > JAN Yellow 4
> > JAN Yellow 4
> > SEP TransX 5
> > FEB Yellow 5
> > FEB Yellow 5
> >
> > In another cell, for example, I would like to put a formula that would look
> > up all of "Yellow" in this list and then give me the average of the numbers
> > for Yellow. I think it's simple enough but I'm not versed enough! Connie

[Duke Carey]

If the data starts in row 5, and the Yellow text is in column A, the values
in column B, use

=SUMPRODUCT(--(A5:A25="Yellow"),--(B5:B25<=7))



"Connie Martin" wrote:

> Thank you, this works! Another question, if you don't mind. Using the
> sample columns below, I need a formula that looks for Yellow and counts the
> number of times it was 7 or less from column C. In looking at the little
> table below, the answer would be 9. for SFS it would be 2, for FFE it would
> be 1. They will be all separate formulas, of course. I only need one
> example---for Yellow.
>
> Yellow 5
> Yellow 6
> SFS 3
> Yellow 6
> Yellow 6
> Yellow 6
> Yellow 6
> Yellow 7
> Yellow 7
> Yellow 7
> Yellow 8
> FFE 7
> FFE 11
> FFE 11
> SFS 3
>
>

[Connie Martin]

Thank you, Duke. That works great! What a great resource this newsgroup is!
Wow! I'm impressed. Thanks to both of you! Connie

"Duke Carey" wrote:

> If the data starts in row 5, and the Yellow text is in column A, the values
> in column B, use
>
> =SUMPRODUCT(--(A5:A25="Yellow"),--(B5:B25<=7))
>
>
>
> "Connie Martin" wrote:
>
> > Thank you, this works! Another question, if you don't mind. Using the
> > sample columns below, I need a formula that looks for Yellow and counts the
> > number of times it was 7 or less from column C. In looking at the little
> > table below, the answer would be 9. for SFS it would be 2, for FFE it would
> > be 1. They will be all separate formulas, of course. I only need one
> > example---for Yellow.
> >
> > Yellow 5
> > Yellow 6
> > SFS 3
> > Yellow 6
> > Yellow 6
> > Yellow 6
> > Yellow 6
> > Yellow 7
> > Yellow 7
> > Yellow 7
> > Yellow 8
> > FFE 7
> > FFE 11
> > FFE 11
> > SFS 3
> >
> >
>

[Duke Carey]

This is an arrya formula, one you enter with Shift+Ctrl+Enter

=AVERAGE(IF(B2:B13="Yellow",C2:C13))

where your data starts in row 2


"Connie Martin" wrote:

> I assume I use a lookup function, but not sure. I would define the range
> with a name, I know that much, but the formula stumps me. I have a list that
> could read something like this:
>
> JAN Con-Way 2
> JAN SFS 3
> APR SFS 3
> JUN SFS 3
> JUL TransX 4
> JAN Yellow 4
> JAN Yellow 4
> JAN Yellow 4
> JAN Yellow 4
> SEP TransX 5
> FEB Yellow 5
> FEB Yellow 5
>
> In another cell, for example, I would like to put a formula that would look
> up all of "Yellow" in this list and then give me the average of the numbers
> for Yellow. I think it's simple enough but I'm not versed enough! Connie

[Connie Martin]

This formula works when the columns are sorted a certain way, but when I sort
them differently, the formula yields 0. My data starts in row 5 and I
adjusted the formula accordingly, but for some reason it doesn't always work.
I must be doing something wrong. Although I don't understand bpeltzer's
reponse, the formula works and is consistent no matter how I sort the
columns. Thank you for responding. Connie


"Duke Carey" wrote:

> This is an arrya formula, one you enter with Shift+Ctrl+Enter
>
> =AVERAGE(IF(B2:B13="Yellow",C2:C13))
>
> where your data starts in row 2
>
>
> "Connie Martin" wrote:
>
> > I assume I use a lookup function, but not sure. I would define the range
> > with a name, I know that much, but the formula stumps me. I have a list that
> > could read something like this:
> >
> > JAN Con-Way 2
> > JAN SFS 3
> > APR SFS 3
> > JUN SFS 3
> > JUL TransX 4
> > JAN Yellow 4
> > JAN Yellow 4
> > JAN Yellow 4
> > JAN Yellow 4
> > SEP TransX 5
> > FEB Yellow 5
> > FEB Yellow 5
> >
> > In another cell, for example, I would like to put a formula that would look
> > up all of "Yellow" in this list and then give me the average of the numbers
> > for Yellow. I think it's simple enough but I'm not versed enough! Connie