GETPIVOTDATA if column doesn't exist

N

neotokyo

Hi all!

Lets say I have a Pivot table with the top being :
Apples Oranges Pears

and the side column being :
Bill
Bob
Joe
Total

To total it, I use
GETPIVOTDATA(table, "Total Orange")+GETPIVOTDATA(table, "Tota
Apple")+GETPIVOTDATA(table, "Total Pears")


The problem is, sometimes one of the columns will have no data(o
change), and therefore be missing from the Pivot table. When thi
happens, the GETPIVOTDATA will return a N/A(even when the other
coumns have data).

Any ideas how to change this so that it adds a 0 instead of just
N/A?
eg. if apples is 0, I want GETPIVOTDATA cell to add 3+0+2=5, instead o
showing N/A.

Keep in mind, my real table has like 30 columns, so using IF statement
would be really tedious.

Any ideas??


THANKS!
 
D

Debra Dalgleish

I don't know of any way, other than IF formulas, to return a zero
instead of an error in your example. You may be able to reduce the
tedium if you can use cell references in the formulas, instead of text
strings. For example, GETPIVOTDATA(table, "Total " & G3)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

getpivotdata syntax 2
getpivotdata function and dates 2
GETPIVOTDATA General 3
getpivotdata 2
GetPivotData does not return total values 8
GETPIVOTDATA problem 1
getpivotdata 1
Getpivotdata function error 1

Top