Trying to get the maximum date in column M if criteria met:

=SUMPRODUCT('WaitTimes'!Q:Q=Sheet1!A2,'WaitTimes'! H:H="1)",'WaitTimes!G:G="B",??? I need to add the maximum date in column M)???

I'm trying to figure out how to get the maximum date in column M if all of
the criteria is met -- can you help me?

Thanks in advance.

Here is the idea where checksD has your dates and the others are the
conditions. Modify to suit and array enter (ctrl +shift+enter)

=MAX(IF(ChecksA=M1,ChecksA<N1,ChecksD))

--
Don Guillett
SalesAid Software

"jhicsupt" wrote in message
...
Trying to get the maximum date in column M if criteria met:

=SUMPRODUCT('WaitTimes'!Q:Q=Sheet1!A2,'WaitTimes'! H:H="1)",'WaitTimes!G:G="B",???
I need to add the maximum date in column M)???

I'm trying to figure out how to get the maximum date in column M if all of
the criteria is met -- can you help me?

Thanks in advance.

a couple of comments in 2003 or earlier you cant use Q:Q as a shorthand in
Sumproduct
Q$1:Q$64000
=SUMPRODUCT(--('WaitTimes'!Q$1:Q$64000=Sheet1!A2),--('WaitTimes'!H$1:H$64000="1),--('WaitTimes!G$1:G$64000="B),--(M$1:M$64000=max(M$1:M$64000)),M$1:M$64000)

"jhicsupt" wrote:

Trying to get the maximum date in column M if criteria met:

=SUMPRODUCT('WaitTimes'!Q:Q=Sheet1!A2,'WaitTimes'! H:H="1)",'WaitTimes!G:G="B",??? I need to add the maximum date in column M)???

I'm trying to figure out how to get the maximum date in column M if all of
the criteria is met -- can you help me?

Thanks in advance.

Try this array** formula:

=MAX((WaitTimes!G1:G10="B")*(WaitTimes!H1:H10=1)*( WaitTimes!Q1:Q10=Sheet1!A2)*WaitTimes!M1:M10)

Format the cell as DATE

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Also note that you *can't* use entire columns as range references unless
you're using Excel 2007

Biff

"jhicsupt" wrote in message
...
Trying to get the maximum date in column M if criteria met:

=SUMPRODUCT('WaitTimes'!Q:Q=Sheet1!A2,'WaitTimes'! H:H="1)",'WaitTimes!G:G="B",???
I need to add the maximum date in column M)???

I'm trying to figure out how to get the maximum date in column M if all of
the criteria is met -- can you help me?

Thanks in advance.

It returns 01/00/00

"T. Valko" wrote:

Try this array** formula:

=MAX((WaitTimes!G1:G10="B")*(WaitTimes!H1:H10=1)*( WaitTimes!Q1:Q10=Sheet1!A2)*WaitTimes!M1:M10)

Format the cell as DATE

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Also note that you *can't* use entire columns as range references unless
you're using Excel 2007

Biff

"jhicsupt" wrote in message
...
Trying to get the maximum date in column M if criteria met:

=SUMPRODUCT('WaitTimes'!Q:Q=Sheet1!A2,'WaitTimes'! H:H="1)",'WaitTimes!G:G="B",???
I need to add the maximum date in column M)???

I'm trying to figure out how to get the maximum date in column M if all of
the criteria is met -- can you help me?

Thanks in advance.

Meant to say -- this still does not work -- it returns 01/00/00. Help!

"jhicsupt" wrote:

It returns 01/00/00

"T. Valko" wrote:

Try this array** formula:

=MAX((WaitTimes!G1:G10="B")*(WaitTimes!H1:H10=1)*( WaitTimes!Q1:Q10=Sheet1!A2)*WaitTimes!M1:M10)

Format the cell as DATE

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Also note that you *can't* use entire columns as range references unless
you're using Excel 2007

Biff

"jhicsupt" wrote in message
...
Trying to get the maximum date in column M if criteria met:

=SUMPRODUCT('WaitTimes'!Q:Q=Sheet1!A2,'WaitTimes'! H:H="1)",'WaitTimes!G:G="B",???
I need to add the maximum date in column M)???

I'm trying to figure out how to get the maximum date in column M if all of
the criteria is met -- can you help me?

Thanks in advance.

If it returns 0 (and you entered it as an array) that means none of the
conditions are being met. If you know for certain that the conditions are
being met then there is a problem with your data. Unseen characters like
leading/trailing spaces. Numbers formatted as TEXT. etc., etc.

Biff

"jhicsupt" wrote in message
...
Meant to say -- this still does not work -- it returns 01/00/00. Help!

"jhicsupt" wrote:

It returns 01/00/00

"T. Valko" wrote:

Try this array** formula:

=MAX((WaitTimes!G1:G10="B")*(WaitTimes!H1:H10=1)*( WaitTimes!Q1:Q10=Sheet1!A2)*WaitTimes!M1:M10)

Format the cell as DATE

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Also note that you *can't* use entire columns as range references
unless
you're using Excel 2007

Biff

"jhicsupt" wrote in message
...
Trying to get the maximum date in column M if criteria met:

=SUMPRODUCT('WaitTimes'!Q:Q=Sheet1!A2,'WaitTimes'! H:H="1)",'WaitTimes!G:G="B",???
I need to add the maximum date in column M)???

I'm trying to figure out how to get the maximum date in column M if
all of
the criteria is met -- can you help me?

Thanks in advance.