can't pull greatest date using criteria

**wendyhatwork** · 10-21-2008, 02:42 PM

working in a workbook and need to summarize on the first page. Each workbook page has the same format, Colume A is the team name, B-J are columes of data and colume K is the completion date. The teams are listed more then 1x each, and I need to pull the greatest date in colume K associated with each team, but only need the team name listed and the greatest date showing in the summary page. I thought I had it with this formula, only to see that it's pulling the greatest date in the range, not the greatest date associated with the team.
=MAX(IF(Queries!A18:A153,"Financial Services",Queries!K18:K153))

**shg** · 10-21-2008, 02:48 PM

Wendy,

Please read the Forum Rules about thread titles before starting your next thread.

**wendyhatwork** · 10-21-2008, 03:00 PM

Thanks SHG! I've updated the title!

**shg** · 10-21-2008, 03:57 PM

Thanks.

You entered this as an array formula, correct? (Hold down the Shift and Ctrl keys, then press Enter.)

**wendyhatwork** · 10-22-2008, 01:21 PM

Originally Posted by shg

Thanks.

You entered this as an array formula, correct? (Hold down the Shift and Ctrl keys, then press Enter.)

yes I did, and got the old #VALUE! error!

**wendyhatwork** · 10-22-2008, 01:34 PM

when I don't enter it as an array formula (without Ctrl, Shft, enter)it will pull the greatest date in the column, but it doesn't use any of the criteria for which team it is.

**shg** · 10-22-2008, 01:39 PM

Post a workbook?

**wendyhatwork** · 10-22-2008, 01:57 PM

I had to cut it up a bit, but you get the idea! (please ignore the summary sheet) I need the dates to autopopulate in the status sheet under "Target Completion Date". These need to be pulled from column K in the workbook pages, but also based on which team in column A.