Excel Maxif

[Tom Hollies via OfficeKB.com]

Hi,

Hi,
I wonder if anyone can help, I have 2 columns say C2:C40 and D240 and I
need to add a function to say D50 that will return the Max for both columns
provided the number is >3 in column C and >25 in column D.
Any Ideas regards Tom

 

[JE McGimpsey]

one way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=MAX(IF(C2:C40>3,C2:C40),IF(D240>25,D240))

 

[Ken Wright]

Do you mean the number has to be in both columns, or that whichever column
it is found in it adheres to the rules for that column alone. What do you
want returned if there is no value that meets the criteria.

 

[Aladin Akyurek]

Are you sure about the criteria you state and the range for which a
conditional max must be calculated? As is, they sound unusual...

 

[Don Guillett]

try this as an array formula which means you must enter with
ctrl+shift+enter

=MAX(MAX(IF(A2:A22>3,A2:A22)),MAX(IF(B2:B22>25,B2:B22)))

 

[Don Guillett]

I like JE's better

 

[Tom Hollies via OfficeKB.com]

Thamks a lot, it worked a treat. I can't say I fully understand why but
that's not unusual for me, thanks again. Tom