X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

Reply to this topicStart new topic
> How To Find The Maximum Value In A Column With Multiple Conditions?, Office 2007    
post Feb 22 2012, 12:25 PM
Post #1

UtterAccess Addict
Posts: 270
From: USA

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!
Go to the top of the page
post Feb 22 2012, 04:36 PM
Post #2

UtterAccess Enthusiast
Posts: 55

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...
Sub GoToMax()
'   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
    End If
'   Determine the maximum value
    MaxVal = Application.Max(Workrange)    
'   Find it and select it
    On Error Resume Next
    Workrange.Find(What:=MaxVal, _
        After:=Workrange.Range("A1"), _
        LookIn:=xlValues, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False _
    If Err <> 0 Then MsgBox "Max value was not found: " & MaxVal
End Sub

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.
Go to the top of the page
post Feb 23 2012, 02:55 PM
Post #3

Utter Access VIP
Posts: 4,841
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.
Go to the top of the page

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:

RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 21st December 2014 - 03:48 PM