I have a problem that shouldn't be a problem but I'm unable to solve it.

My data looks like this:

2012-04-05     1280
2012-04-17     1340
2012-04-20     1510
2012-05-03     1670
2012-05-09     1880

What I want to do is to find the MAX value for april and for may.

So MAX for april should return 1510 and MAX for may should return 1880. Can this be done?

EDIT: Maybe simplified it a bit too much, here is an example closer to what I really want to do:

2012-04-04     14     220
2012-04-11    453     863
2012-04-19    900    1310
2012-05-02   1400    1810
2012-05-15   1900    2250

These are milage from my cars trip computer. I would like to calculate how far I drove each month.

For april: 1310-14 = 1296
For may: 2250-1400 = 850

A simple array formula can do this. If your dates are formatted as dates in Excel, paste the following into a cell and press Ctrl+Shift+Enter:

=MAX(IF(MONTH($A$1:$A$5)=4,B1:B5))

Can this be expanded to also allow 0 to be filtered out? When you use the MIN instead of MAX and your data range has empty values this results into 0 to be reported as the lowest value.

more