Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Dynamic “sum_range” in with SUMIFS function in Excel 2007

Posted on 2011-02-16
3
Medium Priority
?
1,415 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 (Microsoft MVP / EE MVE) earned 2000 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
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

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

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

I came across an unsolved Outlook issue and here is my solution.
If Skype for Business came with your office 2016 or office 365 installation, you may find that it's almost impossible to either disable or remove it. The application will often launch with each start of Windows, even when explicitly configured not t…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

575 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