How to Create a MEDIANIFS User-Defined Function with VBA

Evan and I taking notes from the original Regulators

Did you know that Excel does not have a built-in MEDIANIFS function? You know, like AVERAGEIFS or SUMIFS, just with a median calculation instead of an average or a sum? Your boy didn’t, that’s for sure.

Which is why I’d like to give a HUGE shout to Evan at Mosaic Ventures. Like a gangster, Evan:

1. Identified the missing MEDIANIFS function in the first place
2. Commissioned a VBA-based implementation
3. Gave me the OK to release the code to everyone + dog

Fuck yeah Evan. You’re a renaissance man.

Full disclosure you guys — the ~200 lines below are advanced, but heavily commented. Read through the comments and the process will crystallize in your mind grapes — scout’s honor.

More importantly, the logic for identifying which cells to consider and which to throw away is universal. What does that mean for you?

If you want to, say, build a MODEIFS instead of a MEDIANIFS, you simply have to change ONE LINE. That’s it. One.

So with that in mind, let’s flex those code muscles a little bit:

	Option Explicit
	Public Function MEDIANIFS(median_range As Range, ParamArray range_and_criteria_pairs())
	Dim lngIdx As Long, lngMedianRowIdx As Long, lngCriteriaIdx As Long
	Dim strOperator As String
	Dim varThreshold As Variant, varAccumulator() As Variant
	ReDim varAccumulator(0)
	Dim blnAllMatched As Boolean
	'''''''''''''''
	'Guard clauses'
	'''''''''''''''
	'Check for an empty range
	If median_range Is Nothing Then
	MEDIANIFS = 0
	Exit Function
	End If
	'Check for an uneven number of additional arguments
	'(since we rely on range / criteria pairs)
	If ((UBound(range_and_criteria_pairs) - LBound(range_and_criteria_pairs)) Mod 2) = 0 Then
	MEDIANIFS = 0
	Exit Function
	End If
	'Check for additional range validity (i.e. make sure
	'each passed in criteria range has the same number of rows and
	'columns as the original range
	For lngIdx = LBound(range_and_criteria_pairs) To UBound(range_and_criteria_pairs) Step 2
	If range_and_criteria_pairs(lngIdx).Rows.Count <> median_range.Rows.Count Or _
	range_and_criteria_pairs(lngIdx).Columns.Count <> median_range.Columns.Count Then
	MEDIANIFS = 0
	Exit Function
	End If
	Next lngIdx
	'''''''''''''''''''
	'Process the range'
	'''''''''''''''''''
	'Loop through all rows in the target range
	For lngMedianRowIdx = 1 To median_range.Rows.Count
	'Reset the Match flag, which we will try to flip to True during the Critieria phase
	blnAllMatched = False
	'Loop through all the range / criteria pairs
	For lngCriteriaIdx = LBound(range_and_criteria_pairs) To UBound(range_and_criteria_pairs) Step 2
	'Identify the threshold and the operator for use in the criteria phase
	Select Case Left(range_and_criteria_pairs(lngCriteriaIdx + 1), 2)
	Case Is = "<="
	strOperator = "<="
	varThreshold = Val(Mid(range_and_criteria_pairs(lngCriteriaIdx + 1), 3))
	Case Is = ">="
	strOperator = ">="
	varThreshold = Val(Mid(range_and_criteria_pairs(lngCriteriaIdx + 1), 3))
	'Not equal to (<>) is a little tricky as it could be a number oR a string,
	'so we need to check for both of these conditions
	Case Is = "<>"
	strOperator = "<>"
	If IsNumeric(Mid(range_and_criteria_pairs(lngCriteriaIdx + 1), 1)) And Not _
	IsEmpty(range_and_criteria_pairs(lngCriteriaIdx + 1)) Then
	varThreshold = Val(Mid(range_and_criteria_pairs(lngCriteriaIdx + 1), 3))
	Else
	varThreshold = UCase(Mid(range_and_criteria_pairs(lngCriteriaIdx + 1), 3))
	End If
	Case Else '<~ not a 2-character operator, check only the first character
	Select Case Left(range_and_criteria_pairs(lngCriteriaIdx + 1), 1)
	Case Is = "<"
	strOperator = "<"
	varThreshold = Val(Mid(range_and_criteria_pairs(lngCriteriaIdx + 1), 2))
	Case Is = ">"
	strOperator = ">"
	varThreshold = Val(Mid(range_and_criteria_pairs(lngCriteriaIdx + 1), 2))
	'Equal (=) is a little tricky as it could be a number OR a string,
	'so we need to check for both of these conditions
	Case Is = "="
	strOperator = "="
	If IsNumeric(Mid(range_and_criteria_pairs(lngCriteriaIdx + 1), 1)) And Not _
	IsEmpty(range_and_criteria_pairs(lngCriteriaIdx + 1)) Then
	varThreshold = Val(Mid(range_and_criteria_pairs(lngCriteriaIdx + 1), 2))
	Else
	varThreshold = UCase(Mid(range_and_criteria_pairs(lngCriteriaIdx + 1), 2))
	End If
	'For everything else, we are assuming equality (=) but without the
	'explicit equal sign. This means the If / Else will be very similar
	'to the
	Case Else
	strOperator = "="
	If IsNumeric(range_and_criteria_pairs(lngCriteriaIdx + 1)) And Not _
	IsEmpty(range_and_criteria_pairs(lngCriteriaIdx + 1)) Then
	varThreshold = range_and_criteria_pairs(lngCriteriaIdx + 1)
	Else
	varThreshold = UCase(range_and_criteria_pairs(lngCriteriaIdx + 1))
	End If
	End Select
	End Select
	'Criteria phase: check each cell in the passed-in ParamArray against the threshold
	With range_and_criteria_pairs(lngCriteriaIdx)
	'Check the operator (">=", "<=", "<", ">", "<>", and "=") against the cell
	Select Case strOperator
	Case Is = "<>" '<~ check if cell does not equal the threshold
	If UCase(.Cells(lngMedianRowIdx, 1)) <> varThreshold Then
	blnAllMatched = True
	Else
	blnAllMatched = False
	End If
	Case Is = ">=" '<~ check if cell is greater than or equal to the threshold
	If .Cells(lngMedianRowIdx, 1) >= varThreshold Then
	blnAllMatched = True
	Else
	blnAllMatched = False
	End If
	Case Is = ">" '<~ check if cell is greater than the threshold
	If .Cells(lngMedianRowIdx, 1) > varThreshold Then
	blnAllMatched = True
	Else
	blnAllMatched = False
	End If
	Case Is = "<=" '<~ check if cell is less than or equal to the threshold
	If .Cells(lngMedianRowIdx, 1) <= varThreshold Then
	blnAllMatched = True
	Else
	blnAllMatched = False
	End If
	Case Is = "<" '<~ check if cell is less than the threshold
	If .Cells(lngMedianRowIdx, 1) < varThreshold Then
	blnAllMatched = True
	Else
	blnAllMatched = False
	End If
	Case Else '<~ equal is a special case, could be a number OR a string
	'Examine the numeric, non-blank case
	If IsNumeric(.Cells(lngMedianRowIdx, 1)) And Not _
	IsEmpty(.Cells(lngMedianRowIdx, 1)) Then
	If Val(.Cells(lngMedianRowIdx, 1)) = varThreshold Then
	blnAllMatched = True
	Else
	blnAllMatched = False
	End If
	'Examine the string case
	Else
	If UCase(CStr(.Cells(lngMedianRowIdx, 1))) = varThreshold Then
	blnAllMatched = True
	Else
	blnAllMatched = False
	End If
	End If
	End Select
	End With
	'If our flag has not been flipped to True for even a single iteration,
	'at least one condition has not been met. As such, the cell will
	'NEVER be accumulated!
	If Not blnAllMatched Then Exit For
	Next lngCriteriaIdx
	'Wahoo! If the row passed all criteria (and is numeric),
	'add the value to our accumulator
	If blnAllMatched Then
	With median_range
	If IsNumeric(.Cells(lngMedianRowIdx, 1)) And Not IsEmpty(.Cells(lngMedianRowIdx, 1)) Then
	varAccumulator(UBound(varAccumulator)) = .Cells(lngMedianRowIdx, 1).Value
	ReDim Preserve varAccumulator(UBound(varAccumulator) + 1)
	End If
	End With
	End If
	Next lngMedianRowIdx
	'Remove the last element from the accumulator, it's empty
	ReDim Preserve varAccumulator(UBound(varAccumulator) - 1)
	'Calculate the median
	MEDIANIFS = WorksheetFunction.Median(varAccumulator)
	End Function
	Public Sub RegisterUDF()
	Dim str As String
	str = "Finds median for the cells specified by a given set of conditions or criteria."
	Application.MacroOptions Macro:="MEDIANIFS", Description:=str, Category:=4
	End Sub
	Sub UnregisterUDF()
	Application.MacroOptions Macro:="MEDIANIFS", Description:=Empty, Category:=Empty
	End Sub

view raw medianifs.vb hosted with by GitHub

Here’s a link to the code above so you can review it side-by-side with the walk through below. Right-click, “Open in new window”.

Per usual, we’ll follow the 4-Step VBA Process as we walk through MEDIANIFS:

Step 1 – Setup
Step 2 – Exploration
Step 3 – Execution
Step 4 – Cleanup

To start, Step 1 – Setup is actually minimal. The only real “setup” that we do before getting into step 2 takes place on line 7, where we re-dimension varAccumulator, which will store the values we want to take the median of, to be a single element in size.

This is unfortunately a quirk of VBA — since we don’t know how big varAccumulator will eventually be, it will be repeatedly re-dimensioned, and this two-step declaration is required. (You can read more about the “Array Already Dimensioned” error that results from skipping this step on the Microsoft Support page.)

Nice! With that, we’re ready to move on.

Step 2 – Exploration starts with three guard clauses, occupying lines 10 through 36. Let’s cover each of them:

1. If the user mistakenly passes in an empty Range, we want the function to return 0
2. If the user mistakenly passes in a mismatched number of Range / Criteria pairs, we want the function to return 0
3. If the user mistakenly passes in Ranges that have different numbers of rows or columns, we want the function to return 0

Check #1 takes place from line 15 to line 18. If the median_range, which is passed-in by the user, is Nothing, we assign the output to be 0 and exit the function. Boom.

Check #2 takes place on lines 22 through 25. Every Range the user passes in (like A2:A30) needs to have a Criteria (like “<250”) — this means that there should be an even number of elements in range_and_criteria_pairs. (The name is a giveaway, after all.) We validate that this is true by subtracting the lower bound of the passed-in ParamArray variable range_and_criteria_pairs from the upper bound and making sure the result is an ODD number.

Tricky, right!? Let’s do a quick thought experiment to see why this is correct.

Suppose the user passed in C2:C30 as the Range and “>=5000” as the Criteria. In that case, ParamArray(0) is the C2:C30 Range and ParamArray(1) is “>=5000”. The upper bound is 1 and the lower bound is 0. 1 minus 0 equals 1, which IS evenly divisible by 2 — meaning that our check passes!

On the other hand, suppose the user passed in C2:C30 as the first Range, “>=5000” as the first Criteria, and D2:D30 as the second Range, but forgot to enter a second Criteria. In that case, the upper bound is 2 and the lower bound is 0. 2 minus 0 equals 2, which IS evenly divisble by 2 — meaning our check fails! Phew!

Check #3, the final guard clause, is on lines 30 through 36. For the MEDIANIFS function to work correctly, all of the passed-in Ranges need to be the same size and shape. (This is the case for SUMIFS and AVERAGEIFS too.) Verifying that, fortunately, is a snap — on line 31 we simply validate that the number of rows and columns in the passed-in Ranges match the number of rows and columns in the median_range, which was passed-in by the user and holds the column he or she would like to see the median of.

Wahoo! You’ve already come a long way, so take a second to celebrate. Seriously — that was some heavy data validation! Hats off to ya

Our Step 2 – Exploration begins in a new direction on line 43, where we start looping through all of the rows that are potentially going to be added to our median calculation.

Upon entering this loop, we immediately initialize blnAllMatched to False. We will use blnAllMatched to quickly identify situations where the Criteria is not met — but before then, we need to figure out what kind of operation (like less than or equal to) we’ll be conducting, as well as the threshold (like 200 or “Apples”) for that operation, for each Range / Criteria pair! On line 49, we start looping through the range_and_criteria_pairs, since the user can pass in as many as he or she would like.

For each Range / Criteria pair, we run through the beast mode Select Case statement from line 52 to line 96 to identify the operation (like less than or equal to) and the threshold (like 200 or “Apples”).

This Monster Case Statement…

Lines 53 to 58 are very straightforward — if the first two characters of the Criteria string are less than or equal to, greater than or equal to, the operator (stored in strOperator) is assigned and the threshold (stored in varThreshold) is extracted.

Lines 62 through 69 handle the not equal to sign. As indicated by the comments, this is a little tricky — the user could be trying to match a number (like “<>200”) or a string (like “<>Apples”)! To handle both cases, we use the handy IsNumeric built-in function (as well as the equally-handy IsEmpty function, since IsNumeric evaluates to true on empty cells) on lines 64 and 65.

Lines 74 through 79 function the same way as the first two clauses of the our original Select Case statement — if the first character of the criteria string is less than or greater than, we again assign the strOperator variable and extract the varThreshold.

Lines 83 through 89 handle the equal sign, which is tricky — just like the not equal to sign. We use the same IsNumeric / Not IsEmpty strategy here on lines 85 and 86 to tell the difference between numbers and strings.

Finally, lines 95 to 102 handle everything else. Just like the comments say, we’re going to bucket everything else as an equality check — fortunately, this means that we can use the exact same logic from the equal sign check! (The only difference is that in this case we do not need to chop off the equal sign character.) Nice!

Hell yeah! At this point, you have an operator (stored as strOperator) and a threshold (stored as varThreshold) — all that’s left to do is check the cell value against the operator and threshold! This is where the blnAllMatched variable comes in handy too.

At last y’all, it’s time for Step 3 – Execution, which occurs between lines 108 and 189. Let’s get it…

Once again, we’re going to use a Select Case statement from lines 111 to 160 — this time, though, we’re checking the strOperator variable, applying logic as needed.

On line 112, for example, we handle “<>” (does not equal) by checking the cell value to see if it is not equal to varThreshold — if so, blnMatched is set to True because that Range / Criteria pair was successful!

Line 118 handles “>=” (greater than or equal to) the exact same way — same for line 124 (“>”, greater than), line 130 (“<=”, less than or equal to), all the way through the end of the Select Case statement on line 161. In fact, you might even be thinking that this section is very similar to the last section of Step 2 – Exploration — and you’d be right!

Line 168 is critical here you guys, so listen up. If blnAllMatched is False at the end of a check for ANY Range / Criteria pair, we know that this cell will NEVER be added to median calculation! If that’s the case, we can break the Range / Criteria loop (which is using lngCriteriaIdx) and move on to the next row.

Almost there you guys! Lines 174 through 181 handle value accumulation — let’s break that down.

On line 174, we check to see that blnAllMatched is True. If so, we know that every Range / Criteria check was successful, so we’re PROBABLY going to add the median_range value to our accumulator array, varAccumulator.

On line 176, we make sure that the value from median_range is numeric (since you can only calculate a median on numbers) and not empty (again, since you can only calculate a median on numbers). If the value passes those checks, we add it to the end of varAccumulator, then increase the size of varAccumulator by one on lines 177-178.

Once we get to line 186, our code has worked its way through every row! Since the last time a value was added to varAccumulator we increased its size by one, we know that the last element is empty — which is why we chop it off.

Finally, we take advantage of WorksheetFunction.Median, which calculates the median of varAccumulator like a champ. This is where you can substitute any statistic you’d like — for example, if you want to get the mode instead of the median, simply call WorksheetFunction.Mode instead! So right, so tight…

(The last few lines, from 193 to 201, are “nice to haves” — they add a description to the MEDIANIFS function and drop it into the “Statistics” group.)

PHEW! That was a lot. In case you’d like a visual review, here’s a 12-minute video of me walking through the same steps and demo-ing the new functionality:

Is your MEDIANIFS function humming along? Are you remixing it to build a different IFS function? If not, let me know and I’ll help you get what you need! And if you’d like more step-by-step, no-bullshit VBA guides delivered direct to your inbox, join my email newsletter below.