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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   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

Posts: 336
Joined: 8-December 09
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

Posts: 55
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...
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

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

Custom Search
RSSSearch   Top   Lo-Fi    23rd January 2017 - 05:38 AM