Solved

Dynamic “sum_range” in with SUMIFS function in Excel 2007

Posted on 2011-02-16
3
1,380 Views
Last Modified: 2012-07-06
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
0
Comment
Question by:KP_SoCal
  • 2
3 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 34911786
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
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34911802
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
 

Author Closing Comment

by:KP_SoCal
ID: 34911809
Ms. Teylyn,
This is precisely what I needed!  Thanks so much!!!

KP

P.S. Also thank you for the extra explanation.  =)
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question