Lookup

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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))

 

[Guest]

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