software

I have a worksheet similar to the following:
ABCDE
YearMonthDayHourValue
2000115
2000120
2000111
2000218
200023
2000230
2000314
200035
200036
The worksheet continues on including values for every hour of every day
until the present time. I want to summarize on another worksheet the maximum
value in column E for each month for each year.
Year/Month123
2000
2001
2002
Any suggestions?


For a year in F2 (e.g. 2000) and month in G2 (e.g. 1)

=MAX(IF(($A$2:$A$1000=F2)*($B$2:$B$1000=G2),$E$2:$ E$1000))

confirmed with CTRL SHIFT ENTER

enter all your year month combinations in columns F and G and copy
formula down--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=521250Thanks, I got this to work. What I really need is a summary table on another
sheet. I've tried to modify the formula in cell B2 like this,
=MAX(IF((Sheet1!$A$2:$A$61370=$A3)*(Sheet1!$B$2:$B $61370=B$2),Sheet1!$G$2:$G$61370))
ABCDE
1Y/M1234
21999
32000
42001
I cannot get it to work. Any other suggestions?
Thanks,
tlc
quot;daddylonglegsquot; wrote:

gt;
gt; For a year in F2 (e.g. 2000) and month in G2 (e.g. 1)
gt;
gt; =MAX(IF(($A$2:$A$1000=F2)*($B$2:$B$1000=G2),$E$2:$ E$1000))
gt;
gt; confirmed with CTRL SHIFT ENTER
gt;
gt; enter all your year month combinations in columns F and G and copy
gt; formula down
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=521250
gt;
gt;