Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|