VBA To Show Detail For A SUMIFS Function
What This VBA Code Does
I had a colleague at work come to me with some code he had found on the MrExcel forums that wasn’t quite working with his formulas. The ask was to create a VBA macro that would allow him to essentially mimic the double-clicking function of a Pivot Table to show the detail of a summarized number, but for SUMIFS formulas.
The VBA code was to be run on a single cell’s formula and instantly navigate the user to the source data and filter using just the criteria within the SUMIFS function. This would show the user the detail (other fields in the source data’s table) associated with the results of the SUMIFS function being used within the cell.
The solution proposed in the forum worked for very simplistic formulas with only the use of a single SUMIFS function within the formula and no additional math. For example, the following would work:
=SUMIFS($C$3:$C$14,$B$3:$B$14,"Ohio")But this would not work:
=SUMIFS($C$3:$C$14,$B$3:$B$14,"Ohio")/10This is because the proposed solution was not accurately extracting just the SUMIFS piece from the formula string. I used my limited knowledge of Regular Expressions within my VBA solution to build upon the code and make it more usable.
Below is the modified solution I came up with. You simply run it on any selected cell with a formula utilizing the SUMIFS function and it will filter the source data according.
VBA Code:
Sub DetailForSUMIFS()
'PURPOSE: Display Data Making up a SUMIFS result (similar to a Pivot Table Double-Click)
'INSPIRATION: www.mrexcel.com/forum/excel-questions/616156-drilling-down-into-sumifs-formula.html
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault
Dim SumRange As Range
Dim CriteriaRange As Range
Dim CriteriaValue As Variant
Dim DataSheet As Worksheet
Dim TargetCell As Range
Dim FormulaString As String
Dim TestExpression As String
Dim objRegEx As Object
Dim RegExResult As Object
Dim InputArray As Variant
Dim x As Integer
Dim FirstField As Integer
'Store activecell into a variable
Set TargetCell = ActiveCell
'Ensure cell's formula contains a SUMIFS function
If Not TargetCell.Formula Like "*SUMIFS(*" Then
MsgBox "No SUMIFS Function reference was found. Aborting..."
Exit Sub
End If
'Isolate SUMIFS function via REGEX rule
Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.IgnoreCase = True
objRegEx.Global = True
objRegEx.Pattern = """.*""" ' remove any prior rules
TestExpression = CStr(TargetCell.Formula)
'Isolate anything between "SUMIFS(" and ")"
objRegEx.Pattern = "SUMIFS\((.*?)\)"
'objRegEx.Pattern = "(?<=SUMIFS\()(.*)(?=\))" '>> not sure why this rule does not work...
'Gather Result from RegEx Rule (only using first match)
If objRegEx.test(TestExpression) Then
Set RegExResult = objRegEx.Execute(TestExpression)
If RegExResult.Count > 0 Then
For Each Match In RegExResult
FormulaString = Match.Value
Exit For
Next Match
End If
Else
Exit Sub 'RegEx Rule failed to find anything
End If
'Split SUMIFS function inputs via "," and store in Array variable
FormulaString = Replace(FormulaString, "SUMIFS(", "")
FormulaString = Left(FormulaString, Len(FormulaString) - 1)
InputArray = Split(FormulaString, ",")
'Determine Range of the first Criteria in formula (2nd Input)
Set CriteriaRange = Range(InputArray(1))
'Pull Sheet Reference for where data is stored from Criteria Range
With CriteriaRange
Set DataSheet = Workbooks(.Parent.Parent.Name).Worksheets(.Parent.Name)
End With
'Remove any existing filters on data & turn filtering on (if applicable)
If DataSheet.AutoFilterMode And DataSheet.FilterMode Then
DataSheet.ShowAllData 'Clear Filters
ElseIf Not DataSheet.AutoFilterMode Then
CriteriaRange.CurrentRegion.AutoFilter 'Turn on Filters
End If
'Apply SUMIFS Filtering to Source Data
For x = 1 To UBound(InputArray)
'Ensure we are looking at only Criteria range-related inputs (#2, #4, etc...)
If x Mod 2 <> 0 Then
'Determine Location of first column in Source Data
FirstField = DataSheet.Range(InputArray(x)).Column - DataSheet.AutoFilter.Range.Columns(1).Column + 1
'Determine Criteria Value to Filter Data by
CriteriaValue = Evaluate(InputArray(x + 1))
DataSheet.Range(InputArray(x)).AutoFilter Field:=FirstField, Criteria1:=CriteriaValue
End If
Next x
'Store SUMIFS first input (Sum Range) to a variable
Set SumRange = Range(InputArray(0))
'Select the SUM Range to display total amout in Excel's Status Bar
Application.Goto SumRange
'Scroll to the top of the data set
ActiveWindow.ScrollRow = 1
End SubExample of How This Works:
Here is a simple setup, where my SUMIFS formula is summing all the orders associated with the state of Ohio.
After running the macro, you see the Ohio data is filtered and the sum range is highlighted. This allows me to look at the bottom right corner of my Excel window and easily see that the sum of the filtered data matches the result my SUMIFS formula is coming up with. Pretty cool, huh?
Using VBA Code Found On The Internet
Now that you’ve found some VBA code that could potentially solve your Excel automation problem, what do you do with it? If you don’t necessarily want to learn how to code VBA and are just looking for the fastest way to implement this code into your spreadsheet, I wrote an article (with video) that explains how to get the VBA code you’ve found running on your spreadsheet.
Getting Started Automating Excel
Are you new to VBA and not sure where to begin? Check out my quickstart guide to learning VBA. This article won’t overwhelm you with fancy coding jargon, as it provides you with a simplistic and straightforward approach to the basic things I wish I knew when trying to teach myself how to automate tasks in Excel with VBA Macros.
Also, if you haven’t checked out Excel’s latest automation feature called Power Query, I have put together a beginner’s guide for automating with Excel’s Power Query feature as well! This little-known built-in Excel feature allows you to merge and clean data automatically with little to no coding!
How Do I Modify This To Fit My Specific Needs?
Chances are this post did not give you the exact answer you were looking for. We all have different situations and it’s impossible to account for every particular need one might have. That’s why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to get quick answers to complex problems in Excel, PowerPoint, VBA, you name it!
I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee that 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don’t have time to fit everyone’s questions in (there never seem to be quite enough hours in the day!).
I wish you the best of luck and I hope this tutorial gets you heading in the right direction!
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
![4 PRO Ways to Create A Filtering Search Box [Excel Download]](https://www.thespreadsheetguru.com/wp-content/uploads/2022/10/ExcelFilteringSearchBox.png)
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.