How to count ALL data from the field in pivot?

**Efendi** · 06-26-2009, 05:31 AM

Hi there,

could you help with this please? I have a list of products in 4 countries + its revenues. Each product is in each country. I created pivot table to see how much of particular product was sold in particular country. This is used by GETPIVOTDATA function.

Now my question is - how I can get total revenue for particular product by using GETPIVOTDATA function? It does not work, because Excel always wants me to add field Country into the function. Please see an attachment, that maybe helps, what I need.

THANX for any help!

**DonkeyOte** · 06-26-2009, 08:28 AM

Embed IFs to generate the various functions... ie if not required the field should be omitted from the Function altogether meaning you need different functions based on the combination of selections.

As I see it you have 4 possible combinations:

Country Specific & Product Specific (all fields incl in Function)
Country Specific & Non Product Specific (no Product field in Function)
Non Country Specific & Product Specific (no Country field in Function)
Non Country Specific & Non Product Specific (neither Country nor Product field in Function)

**DonkeyOte** · 06-26-2009, 08:53 AM

Given you're running XL2007 you could equally just run some SUMIFS against your source table rather than querying the PT directly, perhaps a little more flexible ?

C32:
=SUMIFS(Table1[Revenue],Table1[Product],IF($B$30=1,"*",$C$30),Table1[Country],IF($B$26=1,"*",$C$26))

If you want to persist with the GETPIVOTDATA feature one other alternative to running multiple functions within an IF would be to use something along the lines of:

C31:
="GETPIVOTDATA(""Revenue"",$F$2"&REPT(",""Country"""&",$C$26",$B$26<>1)&REPT(",""Product"""&",$C$30",$B$30<>1)&")"

Then create a defined name

Name: =_Evaluate
RefersTo: =EVALUATE(INDIRECT("R[-1]C",False))

Then in C32

=_Evaluate

As you alter your options you should find the result in C32 updates accordingly (as the text string in C31 adjusts and is in turn Evaluated) ... though using this approach I would make the points that:

a) you should list Product filter ahead of Country

b) ALL option for Product should be removed

both of the above are important given Product is a pre-requisite (and given layout of PT can not be ALL).