range usage question

J

jzingman

I want the maximum of column A when column B contains a certain word. I tried
=max(A:A*(B:B="word"))

but get 0. Thinking that the B reference was the problem, I tried

=max(B:B="word")

and get 0, although if I choose a cell that contains word,

=max(B5="word")

I get 1. So clearly I don't know how to do this reference. What's the
right way?

Thanks
 
J

jzingman

Thanks, but that returns the sum over all the values, not the maximum of the
individual vales.
 
P

pdberger

jz --

Here's something that works, but you can't use if for an entire column:

=sumproduct(A1:A100,--(B1:B100="word"))

HTH
 
T

T. Valko

Try this array formula** :

=MAX(IF(B1:B10="word",A1:A10))

Note that you can't use entire columns as range references unless you're
using Excel 2007.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
B

Bob Phillips

=MAX(IF(B1:B1000="word",A1:A1000))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

You cannot use a whole column in array formulae (prior to excel 2007), but
must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
J

jzingman

This works. Thanks to all who replied. Doesn't seem especially obvious to
me, but live and learn.
 
Top