MAX, IF multiples

H

holyman

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
 
D

Dave Peterson

=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)
 
P

Pete_UK

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
 
H

holyman

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
 
B

Bob Phillips

=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)
 
P

Pete_UK

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
 
H

holyman

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