Solved

Dynamic “sum_range” in with SUMIFS function in Excel 2007

Posted on 2011-02-16
3
1,370 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:
teylyn 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:teylyn
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now