×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel - getpivotdata function

Excel - getpivotdata function

Excel - getpivotdata function

(OP)
Hi
Is there a way to sum multiple items from a powerpivot when it is referencing to 2 cells?

A1 = Pivot data field
B1 = Pivot data
C1 = Field 1
D1 = Item 1
E1 = Field 2
F1 = Item 2
G1 = Item 1a
H1 = Item 1b

Formula = getpivotdata(A1&"",M1,C1,D1,E1,F1) - If referencing to only Item 1, it works
(M1 = Row Label where the powerpivot is located)

Formula = getpivotdata(A1&"",M1,C1,{"[{xxx],[yyy],[zzz]"},E1,F1) - If referencing to Item with actual cell text, it works

However, if referencing to cell range, it doesn't work
I.e. Formula = getpivotdata(A1&"",M1,C1,{D1,G1,H1},E1,F1) - If referencing to only Items with actual cell text, it works

Thanks,
arv

RE: Excel - getpivotdata function

Arv,

Pivot Tables are a great "quick n dirty" aggregation tool.

But for getting aggregations in general I've been using the SUMPRODUCT() function, which does what GETPIVOTDATA() does and lots, LOTS more like also replacing SUMIFS() and COUNTIFS() and, at least to me, SUMPRODUCT() is much MUCH more intuitive.

This assumes that you have the source data table that your PT is aggregating. In my world, ALL my source tables are Structured Tables. It makes your data analysis and reporting applications more nearly self documenting.

With reference to this specific issue...
What happens in vagueness stays in vagueness!

Please provide specific data and the specific results you expect.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Excel - getpivotdata function

So I don't know what your data looks like.

Here's mine...

Source Data...

Country  Salesperson	Order Date	OrderID	Order Amount
USA	 Fuller	        1/1/09	        10392	1440
UK	 Gloucester	1/2/09	        10397	716.72
UK	 Bromley        1/2/09	        10771	344
USA	 Finchley	1/3/09	        10393	2556.95
USA	 Finchley	1/3/09	        10394	442
UK	 Gillingham	1/3/09	        10395	2122.92
USA	 Finchley	1/6/09	        10396	1903.8
USA	 Callahan	1/8/09	        10399	1765.6
USA	 Fuller	        1/8/09	        10404	1591.25
USA	 Fuller	        1/9/09	        10398	2505.6
USA	 Coghill	1/9/09	        10403	855.01
USA	 Finchley	1/10/09	        10401	3868.6
USA	 Callahan	1/10/09	        10402	2713.5
UK	 Rayleigh	1/13/09	        10406	1830.78
USA	 Callahan	1/14/09	        10408	1622.4
USA	 Farnham	1/14/09	        10409	319.2
USA	 Farnham	1/15/09	        10410	802
...
 

Pivot Table


Sum of Order Amount  Column Labels		
Row Labels           UK	        USA	Grand Total
2009
Jan	             $15,176	$49,915	$65,090
Feb	              $2,896	$28,546	$31,442
Mar	              $7,167	$40,347	$47,514
Apr	             $17,992	$13,605	$31,596
May	             $13,171	$50,804	$63,975
Jun	             $11,860	$38,223	$50,083
Jul	             $11,452	$26,093	$37,545
Aug	             $13,009	$43,259	$56,268
Sep	             $19,546	$30,962	$50,507
Oct	             $23,850	$50,455	$74,305
Nov	             $13,886	$26,557	$40,443
Dec	              $6,838	$53,582	$60,420
Grand Total         $156,844   $452,347	$609,191
 

Contrasting formulas
=GETPIVOTDATA(
"Order Amount",$A$4,
"Order Date",ROW(A1),
"Years",2009)

=SUMPRODUCT(
(TEXT(source_data[Order Date],"mmm")=A7)*
(YEAR(source_data[Order Date])=--$A$6)*
(source_data[Order Amount]))


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Excel - getpivotdata function

(OP)
Hi Skip
Thanks for the information.
I dont have access to the source data. The pivot table is a power pivot supplied.
I have attached an example of what i am trying to achieve.

1. I need a formula in column D to handle the multiple items
2. Column N to P are just formula where i am trying to determine Item start cell and Item finish cell to see if i can use this into the formula (given that some rows will have 1 item, some with 2 items, some with 3 items, some with 4 items, etc..)
3. Note that Incidentals at this stage is only associated with Type 4. At some point in time, it may be used. The formula ideally can tackle this requirement as well.

Thanks,
arv

RE: Excel - getpivotdata function

Quote (Arv)

I dont have access to the source data.

Your source data is in A2:C18. However, your PT does NOTHING. It aggregates NOTHING. It is useless!

What is it that you want to do? Forget Pivot Tables and formulas! What question do you want to answer?

Actually, your Data Table looks like an aggregation itself.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Excel - getpivotdata function

Actually this might be a more enlightening PT, maybe, if we knew what question you were answering.


Sum of Amount	Column Labels				
Row Labels	Type 1	Type 2	Type 3	Type 4	Grand Total
Air Fares	2000	1000	100		3100
Fees					
Hotel	        1500	500			2000
Incidentals				100	100
Parking	        50	20	100		170
Taxis	        100	200			300
Uber					
Grand Total	3650	1720	200	100	5670
 



Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Excel - getpivotdata function

Also, your Table in YELLOW HIGHLIGHT makes no sense when compared to your actual data in A2:C18.

Type 4 has only data for Incidentals. But you show Air Fares, Hotel & Taxis
AND Incidentals are listed in Type 2 with parking and air fares???!!!

I just don't get what you're attempting to do.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Excel - getpivotdata function

(OP)

Quote (SkipVought)

Your source data is in A2:C18
- These are not real data. With the real working file, it is some sort of power pivot that is only supplied and I do not have access to any raw data.

Quote (SkipVought)

Type 4 has only data for Incidentals. But you show Air Fares, Hotel & Taxis
- My bad. This should read as Type 1

Quote (SkipVought)

Your source data is in A2:C18
- I intentionally included Incidentals in Type 2 to see if the formula can cater to this situation

I added another scenario in Row 31 (i.e. a gap between J31 and L31).

What i am trying to understand is what formula can be applied to those blue cells so that I can apply them to the real data.

Thanks,
arv

RE: Excel - getpivotdata function

I still don't understand what you're trying to do with this data.

Without reference to pivot tables or formulas, explain what you want to accomplish. So far I have not the foggiest idea.

Maybe it's because there's been a disconnect between your data and your test set in rows 25 and following. Things don't match up, or am I missing something?
Why 2 Type 2 rows?

Did you see my PT? This shows what Categories go with what Classes.
Type 1 & Type 2 contain 4 Categories
Type 3 contains 2 Categories
Type 4 contains 1 Category

Help me out.

Do you need to upload a new corrected workbook?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Excel - getpivotdata function

(OP)
Hi Skip
There are 2 type 2 rows because:
1. Hotel only
2. Incidentals, Parking and Air Fares

Row I25:P31 are just my mappings.
The blue cells are formula that i need.

Yes, I did see your pivot table.

I will still need the formula to be driven by the mappings in I25:P31.

Thanks,
arv

RE: Excel - getpivotdata function

The formula works ONLY when there are VALID values in your Item array.

D26: =IFERROR(SUM(GETPIVOTDATA($J$2&"",$H$2,$G26,$I26,$H26,OFFSET(J26,0,0,1,COUNTA(J26:M26)))),"")


The OFFSET() function returns an array to the last argument for one or more Categories of the GETPIVOTDATA() function.

If you have an INVALID value in the Categories array for a given Class, an ERROR results. You can use Data>Validation to keep the values "clean."

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Excel - getpivotdata function

(OP)
Hi Skip
Thanks heaps. The offset formula is exactly what i need.

Thanks again for your help.

Regards,
arv

RE: Excel - getpivotdata function

The Data > Validation > List can be accomplished with these steps...

1) Define the Structured Table range in the Formulas > Name Manager as...

a) rCategory
=tTYPE[Category]
b) rClass
=tTYPE[Class]

2) Data > Validation > List formula-- selection J26:M30
=OFFSET(rCategory,MATCH($I26,rClass,0)-1,0,COUNTIF(rClass,$I26),1)

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Excel - getpivotdata function

(OP)
Hi Skip
Great stuff.. Thanks for the information for the data validation list.
Works like a charm.

Regards,
arv

RE: Excel - getpivotdata function

Arv,

I've enjoyed working on this challenge of yours.

I like simple solutions.

IMNSHO, the Pivot Table in this particular instance is a totally unnecessary useless appendage. It complicates and clutters your appication.

Back to the SUMPRODUCT() multi-functional function.

I'm looking for simplicity and clarity.

Compare
=IFERROR(SUM(GETPIVOTDATA($J$2&"",$H$2,$G26,$I26,$H26,OFFSET(J26,0,0,1,COUNTA(J26:M26)))),"")
=SUMPRODUCT((tTYPE[Class]=I26)*(tTYPE[Category]=OFFSET(J26,0,0,1,COUNTA(J26:M26)))*(tTYPE[Amount]))
 

As I stated earlier, I like the simplicity of using equalities: =, <>, >, < resulting in TRUE/FALSE results for each (criteria expression) that effects the aggregations in the array.

In working at this problem, I learned a thing or two: one of them being the use of the OFFSET() array function in the SUMPRODUCT() function.

One caveat for your Source Data Table. The Class column must be the last sort, in Ascending order.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Excel - getpivotdata function

(OP)
Hi Skip
Sorry for the late response.
Likewise, I would very much prefer the sumproduct formula as its much clearer and simple.
I.e. if i had the source data handy, that would be my go to option.
However, the complexity is the data source is not avail to me.
I only have access to the power pivot output.

Definitely learnt more than one thing from this post :).

I'll be posting another question shortly. Looking forward to your response.

Thanks again Skip.

Cheers,
arv

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close