Drilling down into SUMIFs formula

[IonGridMaster]

This is a question on Excel 2007.

I have an Excel file that contains a data dump from an external database file with numerous analytical sheets that perform calculations. Some of these calculations utilize the SUMIFs function that was introduced in Excel 2007. This function does an outstanding job of summing a column on the data sheet based on multiple criteria.

However, someone high up in management in my organization would like to "drill down" into the data behind the SUMIFs formulas to get a quick snapshot of the lines in the database that roll into the
SUMIFs formula. =SUMIFS(DataBase!E:E,Data!A:A,C7,DataBase!B:B,D7,DataBase!C:C,E7,DataBase!D:D,F7)

If I double click on a cell with the formula above, Excel takes me to the Database tab and selects Column E which is close, but not exactly what I need. What I really need is for Excel to only show the rows on the database sheet that make up the total in the SUMIFs formula and not the entire data dump from the database.

At present, we have to manually apply the autofilter on multiple columns to show the rows in column E that make up the total in the SUMIFs formula which is a tedious and time consuming task. Is there a way to force Excel to do this? Someone suggested a custom database application or pivot tables, but we do not want to reinvent the wheel.

 

[Joe_S]

IonGridMaster,

I once had to perform the same task you are describing. I was able to locate the code and it is pasted below.

Step 1: Put the following code behind all sheets with the SUMIFs formulas.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
FilterBySUMIFs Target.Cells(1)
End Sub

Please note that it will only work with a plain SUMIFs formula. If your formula has anything other than a standard sumifs formula, it may need to be modified.

Step 2: Paste the code below into a standard VBA module.


Option Explicit



Sub FilterBySUMIFs(r As Range)

Dim v, ctr As Integer
Dim intField As Integer, intPos As Integer
Dim strCrit As String
Dim rngCritRange1 As Range, rngSUM As Range
Dim wksDataSheet As Worksheet

If Not r.Formula Like "*SUMIFS(*" Then Exit Sub

'split formula by comma, strip the right paren
v = Split(Left(r.Formula, Len(r.Formula) - 1), ",")

'the first criteria range is the 2nd element of the array
Set rngCritRange1 = Range(v(LBound(v) + 1))

'use first criteria range to get a reference to the data sheet
With rngCritRange1
Set wksDataSheet = Workbooks(.Parent.Parent.Name).Worksheets(.Parent.Name)
End With

'clear any existing filter, turn filtering on if needed
With wksDataSheet
If .AutoFilterMode And .FilterMode Then
'clear existing autofilter
.ShowAllData
ElseIf Not .AutoFilterMode Then
'display autofilter arrows
rngCritRange1.CurrentRegion.AutoFilter
End If
End With

'set the filters
For ctr = LBound(v) + 1 To UBound(v)
If ctr Mod 2 <> 0 Then
With wksDataSheet
'determine field in case table does not start in column A
intField = .Range(v(ctr)).Column - .AutoFilter.Range.Columns(1).Column + 1
'use evaluate instead of range(v(ctr + 1))
'so both cell-reference and hard-coded criteria are handled.
strCrit = Evaluate(v(ctr + 1))
.Range(v(ctr)).AutoFilter Field:=intField, Criteria1:=strCrit
End With
End If
Next


'strip left paren and everything to left of it, get the sum range from first element of array
intPos = InStr(1, v(LBound(v)), "(")
Set rngSUM = Range(Replace(v(LBound(v)), Left(v(LBound(v)), intPos), ""))
'select the SUM range so total displays in status bar
Application.Goto rngSUM


End Sub

 

[IonGridMaster]

Joe S,

That worked exactly as I needed it to. Fortunately for me, as I wasn't looking forward to having to modify your routines! Thank you.

 

[PurpleSky]

I can't get the codes to work. When I click to debug, it brings me to the following line in the VBA code. Below is my SUMIFS formula. Any advice??

strCrit = Evaluate(v(ctr + 1))

=SUMIFS(CY!$N:$N,CY!$AB:$AB,$E14,CY!$B:$B,"<="&$V$2,CY!K:K,MID($V$1,5,3))/1000