Dynamic “sum_range” in with SUMIFS function in Excel 2007

In the attached excel spreadsheet on the “Results” tab in column B, I’m using a basic SUMIFS function.  You’ll see my “sum range” is set to column Q on my “PivotTable” worksheet tab.

I need to make this formula dynamic due to the fact my “Grand Total” range may not always been in column Q.  Any ideas?
Pivot-Example.xlsx
KP_SoCalAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
Hello,

try in  B2

=INDEX(PivotTable!$A$4:$AZ$100,MATCH(Results!A2,PivotTable!$A$4:$A$100,0),MATCH("Grand Total",PivotTable!$4:$4,0))

copy down.

cheers, teylyn
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
This will cater for a pivot table that has the column labels in row 4, can be up to 52 columns (column AZ) and goes down to row 100. Adjust if required.

It identifies the column by looking for the text "Grand Total"

cheers, teylyn
0
 
KP_SoCalAuthor Commented:
Ms. Teylyn,
This is precisely what I needed!  Thanks so much!!!

KP

P.S. Also thank you for the extra explanation.  =)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.