Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there a MAXIF formula similar to the SUMIF formula?
I have a worksheet similar to the following:
A B C D E Year Month Day Hour Value 2000 1 15 2000 1 20 2000 1 11 2000 2 18 2000 2 3 2000 2 30 2000 3 14 2000 3 5 2000 3 6 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/Month 1 2 3 2000 2001 2002 Any suggestions? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there a MAXIF formula similar to the SUMIF formula?
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: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=521250 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there a MAXIF formula similar to the SUMIF formula?
Thanks, 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)) A B C D E 1 Y/M 1 2 3 4 2 1999 3 2000 4 2001 I cannot get it to work. Any other suggestions? Thanks, tlc "daddylonglegs" wrote: 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: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=521250 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting Excel whilst using the sumif formula to search for part of a word | Excel Discussion (Misc queries) | |||
SumIf formula with complex criteria | New Users to Excel | |||
Modifyiing a SumIF Formula to look for "OLA* | Excel Worksheet Functions | |||
How do I create a formula in Excel that will countif or sumif bef. | Excel Worksheet Functions | |||
Formula help SUM(IF((... | Excel Worksheet Functions |