Combine MAX and IF. What am I doing wrong?

[sirrox]

I am having a really tuff time with creating this forumla.
I have a sheet with;
1. A column of daily dates in 'Sheet1'!$S$7:$S$10000. (these dates go from 1995 to current)
2. A column of corresponding values for those dates in ''Sheet1'!$AB$7:$AB$10000.
I am creating another sheet that will present the min and max values per month of every month for a given year.
So I created a seperate sheet;
1. A column with the start date of each month A1:A15
2. A column with the end date of each month B1:B15
No I tried placing a forumala in E4 that would give me the max value from column 'AB' for dates in january 2009.

{=MAX(IF(AND('Sheet1'!$S$7:$S$10000>=A4,'Sheet1'!$S$7:$S$10000<=B4),'Sheet1'!$AB$7:$AB$10000))}
It keeps returning '0'.
What am I doing wrong?

 

[barry houdini]

Hello sirrox, welcome to MrExcel, you don't want AND, try like this:

=MAX(IF('Sheet1'!$S$7:$S$10000>=A4,IF('Sheet1'!$S$7:$S$10000<=B4,'Sheet1'!$AB$7:$AB$10000)))

confirmed with CTRL+SHIFT+ENTER

 

[sirrox]

Jeeshhh... and just like that. Worked like a charm!

A guy can struggle all day with this stuff. Thank you so much for your welcome and clear instructions!