#  Other Applications & Softwares  > Outlook Formatting & Functions >  >  Conditional Max

## bensonnd

Hello,

I am looking to determine the max value of a column that excludes rows based on a condition of another column. 

ie =MAX(IF($C3>=$S$5,$G$3:$G$66,0))

The only problem with this, is the rows that don't meet the criteria, zero out as needed but are still considered in the overall max function for those that do meet the criteria. These need not apply when calculating max.

I am trying to accomplish computing max(60 various of 99 rows) without sorting because I have to apply this across multiple columns that represent numerous variables. 

Is there anyway to exclude rows in a range where criteria is not met in another range from the max function? 

I would greatly appreciate any help.

Thank you,
Neil

----------


## daddylonglegs

Hello Neil,

I assume your formula should be like this:

=MAX(IF($C3:$C66>=$S$5,$G$3:$G$66))

[you don't need the zero]

This is an "array formula" which means that you won't get the correct result unless entered with CTRL+SHIFT+ENTER

To do that....

Put formula in a cell and select that cell. Then press F2 key and hold down CTRL and SHIFT keys while presing ENTER. Curly braces like { and } will appear around the formula in the formula bar

----------

