Max if statements

[instereo911 via OfficeKB.com]

Hi everyone,

I am having trouble. Searched through threads and couldn't help me.

I have a table that has the following

Date Unit Type Max
10/01/2008 ZZ Associate 5
10/01/2008 AA Associate 10
10/02/2008 ZZ Holding 7
10/02/2008 ZZ Associate 4
10/02/2008 AA Holding 10
10/02/2008 ZZ Associate 15

What i am trying to do is say the following

If Date = 10/02/2008 and if Unit = ZZ and Type = Associate then give me the
max

So for 10/02/2008 - it would be 15

my formula so far (which i can't get is the following):

[=(MAX(IF(AND(A2:A65536=10/02/2008,B2:B65536="ZZ",N2:N65536="Associate"),G2:
G65536,""))))]

What is wrong.. Probably everything

Thanks everyone

 

[Sheila D]

You might find it easier to use the DMAX function
Set up your criteria above the table (repeat the headings exactly as they
are in the table)

Date Unit Type
10/02/2008 ZZ Associate

Then the formula will be =DMAX(database range,column number which returns
Max counting from left to right,criteria) =DMAX(A511,4,A1:C2)
I'm assuming you only want the max for that set of circumstances.....
Hope this helps
Sheila

 

[T. Valko]

Try it like this (array entered):

=MAX(IF((A2:A65536=DATE(2008,10,2))*(B2:B65536="ZZ")*(N2:N65536="Associate"),G2:G65536))

Better if you use cells to hold the criteria:

A1 = 10/2/2008
B1 = ZZ
C1 = Associate

=MAX(IF((A2:A65536=A1)*(B2:B65536=B1)*(N2:N65536=C1),G2:G65536))

 

[muddan madhu]

try this

Col A - Date
Col B - Unit
Col C - Type
Col D - Max

=MAX(IF(A2:A7=DATE(2008,2,10)*(B2:B7="ZZ")*(C2:C7="Associate"),D27,""))
( use ctrl + shift + enter )

 

[instereo911 via OfficeKB.com]

This is exactly what i was looking for. I have most of the data housed on
another sheet so this perfect.

Thanks to both for quick working responses.

Try it like this (array entered):

=MAX(IF((A2:A65536=DATE(2008,10,2))*(B2:B65536="ZZ")*(N2:N65536="Associate"),G2:G65536))

Better if you use cells to hold the criteria:

A1 = 10/2/2008
B1 = ZZ
C1 = Associate

=MAX(IF((A2:A65536=A1)*(B2:B65536=B1)*(N2:N65536=C1),G2:G65536))

Hi everyone,

[quoted text clipped - 26 lines]

Thanks everyone

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

This is exactly what i was looking for. I have most of the data housed on
another sheet so this perfect.

Thanks to both for quick working responses.

Try it like this (array entered):

=MAX(IF((A2:A65536=DATE(2008,10,2))*(B2:B65536="ZZ")*(N2:N65536="Associate"),G2:G65536))

Better if you use cells to hold the criteria:

A1 = 10/2/2008
B1 = ZZ
C1 = Associate

=MAX(IF((A2:A65536=A1)*(B2:B65536=B1)*(N2:N65536=C1),G2:G65536))

Hi everyone,

[quoted text clipped - 26 lines]

Thanks everyone