MAX, IF multiples

holyman · 06-30-2006, 08:35 AM

I have the following formula which works treat.

=MAX(IF(('chassis upload'!$N$1:$N$5000="british gascomm1"),('chassis
upload'!$O$1:$O$5000)))
However, I know need to add more criteria.
I need to add, that if coulmn P is blank, return the highest number from
column O.

i.e the higest number that would be returned is 107, if usuing the same
formula, as 122 is populated by column P, so is not part of the equation.

Column N Column O Column P
BRITISH GASCAR1 183
BRITISH GASCAR0 75 09/06/2006
BRITISH GASCOMM1 106 07/06/2006
BRITISH GASCOMM1 106
BRITISH GASCOMM1 107 07/06/2006
BRITISH GASCOMM1 107 07/06/2006
BRITISH GASCOMM1 107
BRITISH GASCOMM1 122 08/06/2006

Please help. Many thanks

Dave Peterson · 06-30-2006, 08:45 AM

=MAX(IF((('chassis upload'!$N$1:$N$5000="british gascomm1")
*('chassis upload'!$P$1:$P$5000="")),
('chassis upload'!$O$1:$O$5000)))

Just keep multiplying those conditions.

(Still ctrl-shift-entered)

holyman wrote:
>
> I have the following formula which works treat.
>
> =MAX(IF(('chassis upload'!$N$1:$N$5000="british gascomm1"),('chassis
> upload'!$O$1:$O$5000)))
> However, I know need to add more criteria.
> I need to add, that if coulmn P is blank, return the highest number from
> column O.
>
> i.e the higest number that would be returned is 107, if usuing the same
> formula, as 122 is populated by column P, so is not part of the equation.
>
> Column N Column O Column P
> BRITISH GASCAR1 183
> BRITISH GASCAR0 75 09/06/2006
> BRITISH GASCOMM1 106 07/06/2006
> BRITISH GASCOMM1 106
> BRITISH GASCOMM1 107 07/06/2006
> BRITISH GASCOMM1 107 07/06/2006
> BRITISH GASCOMM1 107
> BRITISH GASCOMM1 122 08/06/2006
>
> Please help. Many thanks

--

Dave Peterson

Pete_UK · 06-30-2006, 08:50 AM

Try this:

=MAX(IF(('chassis upload'!$P$1:$P$5000="")*('chassis
upload'!$N$1:$N$5000="british gascomm1"),('chassis
upload'!$O$1:$O$5000),0))

As this is an array formula, you will have to use CTRL-SHIFT-ENTER
instead of the usual ENTER once you have typed it in (or subsequently
edit it). If you do this correctly, then Excel will wrap curly braces {
} around the formula - you must not type these yourself.

Hope this helps.

Pete

holyman wrote:
> I have the following formula which works treat.
>
> =MAX(IF(('chassis upload'!$N$1:$N$5000="british gascomm1"),('chassis
> upload'!$O$1:$O$5000)))
> However, I know need to add more criteria.
> I need to add, that if coulmn P is blank, return the highest number from
> column O.
>
> i.e the higest number that would be returned is 107, if usuing the same
> formula, as 122 is populated by column P, so is not part of the equation.
>
> Column N Column O Column P
> BRITISH GASCAR1 183
> BRITISH GASCAR0 75 09/06/2006
> BRITISH GASCOMM1 106 07/06/2006
> BRITISH GASCOMM1 106
> BRITISH GASCOMM1 107 07/06/2006
> BRITISH GASCOMM1 107 07/06/2006
> BRITISH GASCOMM1 107
> BRITISH GASCOMM1 122 08/06/2006
>
> Please help. Many thanks

holyman · 06-30-2006, 09:20 AM

Superb, many thanks guys. I've spent hours trying to do that, but never but
a * in it.

How would a count work then. If need to find the number of British
Gascomm1, that did not have a date in column P

"Pete_UK" wrote:

> Try this:
>
> =MAX(IF(('chassis upload'!$P$1:$P$5000="")*('chassis
> upload'!$N$1:$N$5000="british gascomm1"),('chassis
> upload'!$O$1:$O$5000),0))
>
> As this is an array formula, you will have to use CTRL-SHIFT-ENTER
> instead of the usual ENTER once you have typed it in (or subsequently
> edit it). If you do this correctly, then Excel will wrap curly braces {
> } around the formula - you must not type these yourself.
>
> Hope this helps.
>
> Pete
>
> holyman wrote:
> > I have the following formula which works treat.
> >
> > =MAX(IF(('chassis upload'!$N$1:$N$5000="british gascomm1"),('chassis
> > upload'!$O$1:$O$5000)))
> > However, I know need to add more criteria.
> > I need to add, that if coulmn P is blank, return the highest number from
> > column O.
> >
> > i.e the higest number that would be returned is 107, if usuing the same
> > formula, as 122 is populated by column P, so is not part of the equation.
> >
> > Column N Column O Column P
> > BRITISH GASCAR1 183
> > BRITISH GASCAR0 75 09/06/2006
> > BRITISH GASCOMM1 106 07/06/2006
> > BRITISH GASCOMM1 106
> > BRITISH GASCOMM1 107 07/06/2006
> > BRITISH GASCOMM1 107 07/06/2006
> > BRITISH GASCOMM1 107
> > BRITISH GASCOMM1 122 08/06/2006
> >
> > Please help. Many thanks
>
>

Bob Phillips · 06-30-2006, 09:45 AM

=SUMPRODUCT(--('chassis upload'!$P$1:$P$5000=""),
--('chassis upload'!$N$1:$N$5000="british gascomm1"))

NOT an array formula

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"holyman" <[email protected]> wrote in message
news:[email protected]...
> Superb, many thanks guys. I've spent hours trying to do that, but never
but
> a * in it.
>
> How would a count work then. If need to find the number of British
> Gascomm1, that did not have a date in column P
>
> "Pete_UK" wrote:
>
> > Try this:
> >
> > =MAX(IF(('chassis upload'!$P$1:$P$5000="")*('chassis
> > upload'!$N$1:$N$5000="british gascomm1"),('chassis
> > upload'!$O$1:$O$5000),0))
> >
> > As this is an array formula, you will have to use CTRL-SHIFT-ENTER
> > instead of the usual ENTER once you have typed it in (or subsequently
> > edit it). If you do this correctly, then Excel will wrap curly braces {
> > } around the formula - you must not type these yourself.
> >
> > Hope this helps.
> >
> > Pete
> >
> > holyman wrote:
> > > I have the following formula which works treat.
> > >
> > > =MAX(IF(('chassis upload'!$N$1:$N$5000="british gascomm1"),('chassis
> > > upload'!$O$1:$O$5000)))
> > > However, I know need to add more criteria.
> > > I need to add, that if coulmn P is blank, return the highest number
from
> > > column O.
> > >
> > > i.e the higest number that would be returned is 107, if usuing the
same
> > > formula, as 122 is populated by column P, so is not part of the
equation.
> > >
> > > Column N Column O Column P
> > > BRITISH GASCAR1 183
> > > BRITISH GASCAR0 75 09/06/2006
> > > BRITISH GASCOMM1 106 07/06/2006
> > > BRITISH GASCOMM1 106
> > > BRITISH GASCOMM1 107 07/06/2006
> > > BRITISH GASCOMM1 107 07/06/2006
> > > BRITISH GASCOMM1 107
> > > BRITISH GASCOMM1 122 08/06/2006
> > >
> > > Please help. Many thanks
> >
> >

Pete_UK · 06-30-2006, 10:15 AM

Or if you wanted to stick with array formulae, try the following:

=SUM(IF(('chassis upload'!$P$1:$P$5000="")*('chassis
upload'!$N$1:$N$5000="british gascomm1"),1,0))

Again, CTRL-SHIFT-ENTER to commit. Basically, if the condition is met,
then add a 1 to a running sum.

Hope this helps.

Pete

Bob Phillips wrote:
> =SUMPRODUCT(--('chassis upload'!$P$1:$P$5000=""),
> --('chassis upload'!$N$1:$N$5000="british gascomm1"))
>
> NOT an array formula
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "holyman" <[email protected]> wrote in message
> news:[email protected]...
> > Superb, many thanks guys. I've spent hours trying to do that, but never
> but
> > a * in it.
> >
> > How would a count work then. If need to find the number of British
> > Gascomm1, that did not have a date in column P
> >
> > "Pete_UK" wrote:
> >
> > > Try this:
> > >
> > > =MAX(IF(('chassis upload'!$P$1:$P$5000="")*('chassis
> > > upload'!$N$1:$N$5000="british gascomm1"),('chassis
> > > upload'!$O$1:$O$5000),0))
> > >
> > > As this is an array formula, you will have to use CTRL-SHIFT-ENTER
> > > instead of the usual ENTER once you have typed it in (or subsequently
> > > edit it). If you do this correctly, then Excel will wrap curly braces {
> > > } around the formula - you must not type these yourself.
> > >
> > > Hope this helps.
> > >
> > > Pete
> > >
> > > holyman wrote:
> > > > I have the following formula which works treat.
> > > >
> > > > =MAX(IF(('chassis upload'!$N$1:$N$5000="british gascomm1"),('chassis
> > > > upload'!$O$1:$O$5000)))
> > > > However, I know need to add more criteria.
> > > > I need to add, that if coulmn P is blank, return the highest number
> from
> > > > column O.
> > > >
> > > > i.e the higest number that would be returned is 107, if usuing the
> same
> > > > formula, as 122 is populated by column P, so is not part of the
> equation.
> > > >
> > > > Column N Column O Column P
> > > > BRITISH GASCAR1 183
> > > > BRITISH GASCAR0 75 09/06/2006
> > > > BRITISH GASCOMM1 106 07/06/2006
> > > > BRITISH GASCOMM1 106
> > > > BRITISH GASCOMM1 107 07/06/2006
> > > > BRITISH GASCOMM1 107 07/06/2006
> > > > BRITISH GASCOMM1 107
> > > > BRITISH GASCOMM1 122 08/06/2006
> > > >
> > > > Please help. Many thanks
> > >
> > >

holyman · 06-30-2006, 10:25 AM

Spot on....i was totally going down the wrong path, i was trying to do a
countif and if.
Thanks muchly

"Bob Phillips" wrote:

> =SUMPRODUCT(--('chassis upload'!$P$1:$P$5000=""),
> --('chassis upload'!$N$1:$N$5000="british gascomm1"))
>
> NOT an array formula
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "holyman" <[email protected]> wrote in message
> news:[email protected]...
> > Superb, many thanks guys. I've spent hours trying to do that, but never
> but
> > a * in it.
> >
> > How would a count work then. If need to find the number of British
> > Gascomm1, that did not have a date in column P
> >
> > "Pete_UK" wrote:
> >
> > > Try this:
> > >
> > > =MAX(IF(('chassis upload'!$P$1:$P$5000="")*('chassis
> > > upload'!$N$1:$N$5000="british gascomm1"),('chassis
> > > upload'!$O$1:$O$5000),0))
> > >
> > > As this is an array formula, you will have to use CTRL-SHIFT-ENTER
> > > instead of the usual ENTER once you have typed it in (or subsequently
> > > edit it). If you do this correctly, then Excel will wrap curly braces {
> > > } around the formula - you must not type these yourself.
> > >
> > > Hope this helps.
> > >
> > > Pete
> > >
> > > holyman wrote:
> > > > I have the following formula which works treat.
> > > >
> > > > =MAX(IF(('chassis upload'!$N$1:$N$5000="british gascomm1"),('chassis
> > > > upload'!$O$1:$O$5000)))
> > > > However, I know need to add more criteria.
> > > > I need to add, that if coulmn P is blank, return the highest number
> from
> > > > column O.
> > > >
> > > > i.e the higest number that would be returned is 107, if usuing the
> same
> > > > formula, as 122 is populated by column P, so is not part of the
> equation.
> > > >
> > > > Column N Column O Column P
> > > > BRITISH GASCAR1 183
> > > > BRITISH GASCAR0 75 09/06/2006
> > > > BRITISH GASCOMM1 106 07/06/2006
> > > > BRITISH GASCOMM1 106
> > > > BRITISH GASCOMM1 107 07/06/2006
> > > > BRITISH GASCOMM1 107 07/06/2006
> > > > BRITISH GASCOMM1 107
> > > > BRITISH GASCOMM1 122 08/06/2006
> > > >
> > > > Please help. Many thanks
> > >
> > >
>
>
>