MAX IF Formula

[trizzo]

Hello,

Is there any MAX IF formula?

For example, lets say I want to find the max value of range I2:I50, but just in case the following criteria matches: A2:A50="2013" and B2:B50="3" and C2:C50="TOTAL"?

Thanks!

[Pete_UK]

You have to construct an array* formula for it, like this:

=MAX(IF((A2:A50=2013)*(B2:B50=3)*(C2:C50="TOTAL"),I2:I50))

I've assumed that your numbers are proper numbers and not text values, so I've removed the " from around them. Note that each comparison is within its own brackets, and each term is ANDed together using the * symbol, although there are other ways of writing this using multiple-IFs.

*Note that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), instead of the usual <Enter>. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar, but you must not type these yourself. If you need to amend the formula then you will need to use CSE again. An array formula can be copied to other cells in the usual way(s).

Hope this helps.

Pete

[Ace_XL]

Array formula

=MAX(IF((A2:A50=2013)*(B2:B50=3)*(C2:C50="TOTAL"),I2:I50))

[Tony Valko]

Try this array formula**:

=MAX(IF(A2:A50=2013,IF(B2:B50=3,IF(C2:C50="TOTAL",I2:I50))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[trizzo]

Thanks everyone! It worked great!

[Tony Valko]

You're welcome. We appreciate the feedback!