Max if statements
 

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200810/1

Max if statements
 

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(A5:D11,4,A1:C2)
I'm assuming you only want the max for that set of circumstances.....
Hope this helps
Sheila


"instereo911 via OfficeKB.com" wrote:

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200810/1

Max if statements
 

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=C 1),G2:G65536))


--
Biff
Microsoft Excel MVP


"instereo911 via OfficeKB.com" <u27979@uwe wrote in message
news:8c65e36ff7462@uwe...
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200810/1

Max if statements
 

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"),D2:D7,""))
( use ctrl + shift + enter )



On Oct 29, 9:33*pm, "instereo911 via OfficeKB.com" <u27979@uwe wrote:
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

--
Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/ms-excel/200810/1

Max if statements
 

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.

T. Valko wrote:
Try it like this (array entered):

=MAX(IF((A2:A65536=DATE(2008,10,2))*(B2:B65536="Z Z")*(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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200810/1

Max if statements
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"instereo911 via OfficeKB.com" <u27979@uwe wrote in message
news:8c665037bb718@uwe...
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.

T. Valko wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200810/1