How To Find The Maximum Value In A Column With Multiple Conditions?, Office 2007
Feb 22 2012, 12:25 PM
Joined: 8-December 09
need to find the maximum value in a column of a worksheet, where certain values are present in other columns. For example, I would need to find the maximum value in column F among rows where column C = "John Doe", column D = "Assessment", and column E = "1/1/2012". Could someone please suggest the proper strategy to accomplish this?
Thanks so much for your assistance!
Feb 22 2012, 04:36 PM
Joined: 8-February 12
I was browsing around and saw this code by John Walkenbach. I thought it may be of some use to you regarding your question. It uses the .Find function to go to the maximum value...
' Activates the cell with the largest value
Dim WorkRange as Range
' Exit if a range is not selected
If TypeName(Selection) <> "Range" Then Exit Sub
' If one cell is selected, search entire worksheet;
' Otherwise, search the selected range
If Selection.Count = 1 Then
Set Workrange = Cells
Set Workrange = Selection
' Determine the maximum value
MaxVal = Application.Max(Workrange)
' Find it and select it
On Error Resume Next
SearchDirection:=xlNext, MatchCase:=False _
If Err <> 0 Then MsgBox "Max value was not found: " & MaxVal
If you sorted your sheet by "C", "D", then "E" that should group them to where you can make a selection to pass to the 'WorkRange' variable.
Feb 23 2012, 02:55 PM
Utter Access VIP
Joined: 22-June 04
From: North Carolina
It gets ugly.
Here is the single criteria version of MAXIF(). Enter it as an array formula (CTRL-SHIFT-ENTER).
In this case, the thing you want the maxium of is in Range G1:G15. What you want to evaluate is in Range F1:F15. What you want to match to is in Cell A12.
To extend the formula to more criteria, you would have to nest the first if statement.
=MAX(IF($F$1:$F$15=A12,IF(SecondRange = Second Criteria, IF (Third Range = Third Criteria , ...IF(NOT(ISBLANK($G$1:$G$15)),$G$1:$G$15)))
Oleave it as an exercise to the student to figuer out where to close all those extra parenthesis. I think they'd come at the end. Any formula that closes with six parenthesis can't be pretty.
|Search Top Lo-Fi||1st February 2015 - 03:39 AM|