## 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

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

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 dataand thespecific resultsyou expect.Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"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

Here's mine...

Source Data...

Pivot Table

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,

_{ Just traded in my OLD subtlety... for a NUance!}"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

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

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,

_{ Just traded in my OLD subtlety... for a NUance!}"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

maybe, if we knew what question you were answering.Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"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

Type 4 has only data for

Incidentals. But you showAir Fares, Hotel & TaxisAND Incidentals are listed in Type 2 with parking and air fares???!!!

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

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"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

- 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.

Type 4 has only data for Incidentals. But you show Air Fares, Hotel & Taxis

- My bad. This should read as Type 1

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

## RE: Excel - getpivotdata function

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,

_{ Just traded in my OLD subtlety... for a NUance!}"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

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

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,

_{ Just traded in my OLD subtlety... for a NUance!}"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

Thanks heaps. The offset formula is exactly what i need.

Thanks again for your help.

Regards,

arv

## RE: Excel - getpivotdata function

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,

_{ Just traded in my OLD subtlety... for a NUance!}"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

Great stuff.. Thanks for the information for the data validation list.

Works like a charm.

Regards,

arv

## RE: Excel - getpivotdata function

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

simplicityandclarity.Compare

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,

_{ Just traded in my OLD subtlety... for a NUance!}"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

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