## Situation

Yesterday I was creating a report from list of work orders.  Each time the consultant starts a new project a new work order is created.  The report needed two pieces of information

• When each consultant first started at the client
• The consultant's last work day, whether in the past or future

## Found Solution

I tried to write the array formula, but that is not my strength, so I search the Internet for "MinIf", figuring that's what any Excel expert would call such a function.

Sure enough, I found a great article, Finding MIN IF or MAX IF in Excel, that explained the array formulas.

## Easy cheesy!...Oops!....Aha!

As always, I did my best to follow direction.  As usual, Debra (the author), reminded the users to use CTL-SHIFT-ENTER because it was an array formula.

Hmmmm...instead of the date I got a zero!

Looking at my formula versus the example I noticed that I had used whole columns instead of limiting to the rows with data
• Example: {=MIN(IF(\$C\$2:\$C\$8=\$C\$11,\$D\$2:\$D\$8))}
• Mine: {=MIN(IF('Source'!\$A:\$A=\$A2,'Source'!\$D:\$D))}
Once I changed the formula to "{=MIN(IF('Source'!\$A2:\$A264=\$A2,'Source'!\$D2:\$D264))}" it worked fine.

## But I like the way I do things!

My way of looking at data tables and formulas is
• Data usually comes in columns of indeterminate length
• It's quicker to click-choose (or event type) the reference for a column than figure out how many rows to include.
• When adding data to the list, the selected range in the formula may not be updated.

## My Solution...MinIf and MaxIf UDFs

Being old and stuck in my ways, I decided to write the UDFs for these functions.

Function MaxIf(SearchRange As Range, Criteria, ResultRange As Range)
Dim CurrMax
Dim FirstFound As Boolean
Dim i As Double
Dim rngLimitedSearch As Range
Dim rngLimitedResults As Range

If SearchRange.Columns.Count = ResultRange.Columns.Count _
And SearchRange.Rows.Count = ResultRange.Rows.Count _
And (SearchRange.Columns.Count = 1 Or SearchRange.Rows.Count = 1) Then
Set rngLimitedSearch = Intersect(SearchRange, SearchRange.Parent.UsedRange)
Set rngLimitedResults = Intersect(ResultRange, ResultRange.Parent.UsedRange)
For i = 1 To rngLimitedSearch.Cells.Count
If rngLimitedSearch.Cells(i).Value = Criteria Then
If Len(rngLimitedResults.Cells(i).Value) > 0 Then
If Not FirstFound Then
FirstFound = True
CurrMax = rngLimitedResults.Cells(i).Value
Else
If CurrMax < rngLimitedResults.Cells(i).Value Then
CurrMax = rngLimitedResults.Cells(i).Value
End If
End If
End If
End If
Next i
Else
MaxIf = "#Range Err"
End If
If Not FirstFound Then
MaxIf = "#N/A"
Else
MaxIf = CurrMax
End If
End Function

Function MinIf(SearchRange As Range, Criteria, ResultRange As Range)
Dim CurrMin
Dim FirstFound As Boolean
Dim i As Double
Dim rngLimitedSearch As Range
Dim rngLimitedResults As Range

If SearchRange.Columns.Count = ResultRange.Columns.Count _
And SearchRange.Rows.Count = ResultRange.Rows.Count _
And (SearchRange.Columns.Count = 1 Or SearchRange.Rows.Count = 1) Then
Set rngLimitedSearch = Intersect(SearchRange, SearchRange.Parent.UsedRange)
Set rngLimitedResults = Intersect(ResultRange, ResultRange.Parent.UsedRange)
For i = 1 To rngLimitedSearch.Cells.Count
If rngLimitedSearch.Cells(i).Value = Criteria Then
If Len(rngLimitedResults.Cells(i).Value) > 0 Then
If Not FirstFound Then
FirstFound = True
CurrMin = rngLimitedResults.Cells(i).Value
Else
If CurrMin > rngLimitedResults.Cells(i).Value Then
CurrMin = rngLimitedResults.Cells(i).Value
End If
End If
End If
End If
Next i
Else
MinIf = "#Range Err"
End If
If Not FirstFound Then
MinIf = "#N/A"
Else
MinIf = CurrMin
End If
End Function