Thread: Excel Maxif

Hi,

Hi,
I wonder if anyone can help, I have 2 columns say C2:C40 and D2:D40 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

--
Message posted via http://www.officekb.com

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

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


In article <69e745520eff463080a0fc8b7eb6457d@OfficeKB.com>,
"Tom Hollies via OfficeKB.com" <forum@OfficeKB.com> wrote:

> I wonder if anyone can help, I have 2 columns say C2:C40 and D2:D40 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.

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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Tom Hollies via OfficeKB.com" <forum@OfficeKB.com> wrote in message
news:69e745520eff463080a0fc8b7eb6457d@OfficeKB.com...
> Hi,
>
> Hi,
> I wonder if anyone can help, I have 2 columns say C2:C40 and D2:D40 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
>
> --
> Message posted via http://www.officekb.com

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

Tom Hollies via OfficeKB.com wrote:
> Hi,
>
> Hi,
> I wonder if anyone can help, I have 2 columns say C2:C40 and D2:D40 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
>

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
SalesAid Software
donaldb@281.com
"Tom Hollies via OfficeKB.com" <forum@OfficeKB.com> wrote in message
news:69e745520eff463080a0fc8b7eb6457d@OfficeKB.com...
> Hi,
>
> Hi,
> I wonder if anyone can help, I have 2 columns say C2:C40 and D2:D40 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
>
> --
> Message posted via http://www.officekb.com

I like JE's better

--
Don Guillett
SalesAid Software
donaldb@281.com
"Don Guillett" <donaldb@281.com> wrote in message
news:OG8Hv6JDFHA.3908@TK2MSFTNGP12.phx.gbl...
> 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
> SalesAid Software
> donaldb@281.com
> "Tom Hollies via OfficeKB.com" <forum@OfficeKB.com> wrote in message
> news:69e745520eff463080a0fc8b7eb6457d@OfficeKB.com...
> > Hi,
> >
> > Hi,
> > I wonder if anyone can help, I have 2 columns say C2:C40 and D2:D40 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
> >
> > --
> > Message posted via http://www.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

--
Message posted via http://www.officekb.com