MAX IF?
 

can i write a formula that takes the max value in column B but only if column
A has "T" in it...like a SUMIF....
thx.
tami

MAX IF?
 

Try this array formula

=MAX(IF(A1:A20="t",B1:B20))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array


Mike


"Tami" wrote:

can i write a formula that takes the max value in column B but only if column
A has "T" in it...like a SUMIF....
thx.
tami

MAX IF?
 

tami

Try an array formula like

=MAX(--(A1:A9="T")*(B1:B9))

use shift-control-enter to enter. This will give the max value for
numbers in B1:B9, where A1:A9 has the letter T.

Good luck

Ken
Norfolk, Va

On Jun 30, 3:46*pm, Tami wrote:
can i write a formula that takes the max value in column B but only if column
A has "T" in it...like a SUMIF....
thx.
tami

MAX IF?
 

so you have to enter it with a control shift enter but once its written i
don't have to do anything special, correct?....only if i modify the formula
do i have to control shift enter?

"Mike H" wrote:

Try this array formula

=MAX(IF(A1:A20="t",B1:B20))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array


Mike


"Tami" wrote:

can i write a formula that takes the max value in column B but only if column
A has "T" in it...like a SUMIF....
thx.
tami

MAX IF?
 

This one doesn't required ctrl+shift+enter, Just press Enter

=SUMPRODUCT(MAX((A1:A20="T")*B1:B20))



"Tami" wrote:

can i write a formula that takes the max value in column B but only if column
A has "T" in it...like a SUMIF....
thx.
tami

MAX IF?
 

Correct

--
Biff
Microsoft Excel MVP


"Tami" wrote in message
...
so you have to enter it with a control shift enter but once its written i
don't have to do anything special, correct?....only if i modify the
formula
do i have to control shift enter?

"Mike H" wrote:

Try this array formula

=MAX(IF(A1:A20="t",B1:B20))

This is an array formula which must be entered with CTRL+Shift+Enter and
NOT
'just enter. If you do it correctly then Excel will put curly brackets
around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array


Mike


"Tami" wrote:

can i write a formula that takes the max value in column B but only if
column
A has "T" in it...like a SUMIF....
thx.
tami