# Using MAX() in Excel against a list of dates using multiple criteria

I've searched and searched and trialled every answer but none seem to work in my situation.

Using Excel 2003.

I have a table with the columns [DATE], [SITE], [INCIDENT].

I'll explain in something similar to SQL because it's nice and clear. I need to run the following SELECT statement on the data in that table

SELECT [DATE] from Table1 WHERE [SITE] = "Site1" AND [INCIDENT] = "Death"

From that list (multiple values), I then need to display the latest [DATE] value ie. the last date a death occured.

I've tried this following formula:

``````{=MAX(IF('Spreadsheet1'!D3:D1000="Death", IF('Spreadsheet1'!E3:E1000="Site1", 'Spreadsheet1'!A3:A1000, 0), 0))}
``````

But this does not seem to work correctly.

Any help is definitely welcome.

• Is your sheet definitely named "Spreadsheet1", rather than "Sheet1"? Since this is an array formula, are you entering it with the Ctrl-Shift-Enter key combination? – chuff Aug 2 '13 at 0:23
• @chuff makes a good point –– I, also, was able to get your formula to work. – Scott Aug 2 '13 at 0:44
• Works for me too.......Perhaps nitpicking but generically it's better to omit the zeroes, because in other cases if MAX value is a negative number you won't get the right answer, i.e. `=MAX(IF(D3:D1000="Death",IF(E3:E1000="Site1",A3:A1000)))` - that also gives you a syntax that will work for MIN too...... – barry houdini Aug 2 '13 at 15:06

Try

``````=MAX(A3:A1000 * (D3:D1000="Death") * (E3:E1000="Site1"))
``````

Rather than treating your `WHERE` clauses as conditions, this simply treats them as part of the expression that you are maximizing.  TRUE is 1 and FALSE is 0, so value × boolean1 × boolean2 is value if both Booleans are TRUE and 0 otherwise.

Naturally, it’s an array formula; but I see you already understand those.

• Shouldn't this formula have the curly brackets around it `Ctrl` `Shift` `Enter` – wbeard52 Aug 2 '13 at 3:37
• @wbeard52: It’s a question of convention. Yes, if the formula is properly entered with (Ctrl)+(Shift)+(Enter), then it will be displayed with curly braces. But I showed what a user would need to type before pressing the (Ctrl)+(Shift)+(Enter). – Scott Aug 2 '13 at 19:13

Make sure that your sheet name matches the one used in the formula (I assume it does) and that you are entering the formula with the Control-Shift-Enter key combination, since it is an array formula. The formula worked fine for me.