GetPivotData with column grand totals

Hi!

I have a pivot table with the following layout:
[Count of results]
[Status] [Result]
P F Grand Total
A 10 3 13
B 15 8 23
C 5 4 9
Grand Total 30 15 45

A1 = [Count of results]

i am using the GetPivotData function to retrieve the grand totals but i can
only
retrieve the row grand totals (13, 23, 9) and not the column grand totals
(30, 15, 45).
Here is what i tried:
=GETPIVOTDATA(A1, "A") --> 13
=GETPIVOTDATA(A1, "Result") --> 45
=GETPIVOTDATA(A1, "P") --> #N/A
=GETPIVOTDATA(A1, "F") --> #N/A

I have tried using cell references but it doesn't make any difference.
I read many different how-tos about this function but the only solution i
found assumed the presence of column sub-totals.

What am i doing wrong? Help is appreciated

--
Thanks in advance
/Enrico

I think what you want is this:
=GETPIVOTDATA(A1,"Grand Total F")
=GETPIVOTDATA(A1,"Grand Total P")
--
- K Dales


"Enrico Campidoglio" wrote:

> Hi!
>
> I have a pivot table with the following layout:
> [Count of results]
> [Status] [Result]
> P F Grand Total
> A 10 3 13
> B 15 8 23
> C 5 4 9
> Grand Total 30 15 45
>
> A1 = [Count of results]
>
> i am using the GetPivotData function to retrieve the grand totals but i can
> only
> retrieve the row grand totals (13, 23, 9) and not the column grand totals
> (30, 15, 45).
> Here is what i tried:
> =GETPIVOTDATA(A1, "A") --> 13
> =GETPIVOTDATA(A1, "Result") --> 45
> =GETPIVOTDATA(A1, "P") --> #N/A
> =GETPIVOTDATA(A1, "F") --> #N/A
>
> I have tried using cell references but it doesn't make any difference.
> I read many different how-tos about this function but the only solution i
> found assumed the presence of column sub-totals.
>
> What am i doing wrong? Help is appreciated
>
> --
> Thanks in advance
> /Enrico

Try this. GetPivot Data can refenece more than one dimension iun the second
argument...

=GETPIVOTDATA(A1, "Result F")
--
HTH...

Jim Thomlinson


"Enrico Campidoglio" wrote:

> Hi!
>
> I have a pivot table with the following layout:
> [Count of results]
> [Status] [Result]
> P F Grand Total
> A 10 3 13
> B 15 8 23
> C 5 4 9
> Grand Total 30 15 45
>
> A1 = [Count of results]
>
> i am using the GetPivotData function to retrieve the grand totals but i can
> only
> retrieve the row grand totals (13, 23, 9) and not the column grand totals
> (30, 15, 45).
> Here is what i tried:
> =GETPIVOTDATA(A1, "A") --> 13
> =GETPIVOTDATA(A1, "Result") --> 45
> =GETPIVOTDATA(A1, "P") --> #N/A
> =GETPIVOTDATA(A1, "F") --> #N/A
>
> I have tried using cell references but it doesn't make any difference.
> I read many different how-tos about this function but the only solution i
> found assumed the presence of column sub-totals.
>
> What am i doing wrong? Help is appreciated
>
> --
> Thanks in advance
> /Enrico

I tried both:
=GETPIVOTDATA(A1, "Result P")
=GETPIVOTDATA(A1, "Grand Total P")

but I still get #N/A. I noticed though that the above works when there is
only 1 value in the result column i specify in the formula (either P or F).

It seems to me that this formula's behaviour is not really stable, at least
not in Excel 2000 which I am using right now. I read about other users
reporting problems with it.

The reason I started to use it in the first place was to retrieve the totals
from the pivot table into another worksheet. First I thought about simply
doing it in VBA but then I was suggested to use this formula which looked
like a much better solution to me. Anyway I sure wasn't expecting it to be so
complicated...

Do you have any suggestions?

--
Thanks in advance
/Enrico


"Jim Thomlinson" wrote:

> Try this. GetPivot Data can refenece more than one dimension iun the second
> argument...
>
> =GETPIVOTDATA(A1, "Result F")
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Enrico Campidoglio" wrote:
>
> > Hi!
> >
> > I have a pivot table with the following layout:
> > [Count of results]
> > [Status] [Result]
> > P F Grand Total
> > A 10 3 13
> > B 15 8 23
> > C 5 4 9
> > Grand Total 30 15 45
> >
> > A1 = [Count of results]
> >
> > i am using the GetPivotData function to retrieve the grand totals but i can
> > only
> > retrieve the row grand totals (13, 23, 9) and not the column grand totals
> > (30, 15, 45).
> > Here is what i tried:
> > =GETPIVOTDATA(A1, "A") --> 13
> > =GETPIVOTDATA(A1, "Result") --> 45
> > =GETPIVOTDATA(A1, "P") --> #N/A
> > =GETPIVOTDATA(A1, "F") --> #N/A
> >
> > I have tried using cell references but it doesn't make any difference.
> > I read many different how-tos about this function but the only solution i
> > found assumed the presence of column sub-totals.
> >
> > What am i doing wrong? Help is appreciated
> >
> > --
> > Thanks in advance
> > /Enrico

I am using Excel 2000 and the formula I gave works for me regardless of how
many values are in the column. The results are dependent on your table setup
so I would need to know the entire layout. For the values included in the
table, did you choose Sum, Count, Max, Min...? Are the column grand totals
displayed? What is the text showing in the very upper left cell of the table
(e.g. "Sum of Value")? The key to using the formula GETPIVOTDATA is to
exactly match the column headings and row labels from your table - and the
results depend on the function used to summarize your data (for example, are
you showing the Sum of your numbers, or a count, or the min, max, ...?).

--
- K Dales


"Enrico Campidoglio" wrote:

> I tried both:
> =GETPIVOTDATA(A1, "Result P")
> =GETPIVOTDATA(A1, "Grand Total P")
>
> but I still get #N/A. I noticed though that the above works when there is
> only 1 value in the result column i specify in the formula (either P or F).
>
> It seems to me that this formula's behaviour is not really stable, at least
> not in Excel 2000 which I am using right now. I read about other users
> reporting problems with it.
>
> The reason I started to use it in the first place was to retrieve the totals
> from the pivot table into another worksheet. First I thought about simply
> doing it in VBA but then I was suggested to use this formula which looked
> like a much better solution to me. Anyway I sure wasn't expecting it to be so
> complicated...
>
> Do you have any suggestions?
>
> --
> Thanks in advance
> /Enrico
>
>
> "Jim Thomlinson" wrote:
>
> > Try this. GetPivot Data can refenece more than one dimension iun the second
> > argument...
> >
> > =GETPIVOTDATA(A1, "Result F")
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Enrico Campidoglio" wrote:
> >
> > > Hi!
> > >
> > > I have a pivot table with the following layout:
> > > [Count of results]
> > > [Status] [Result]
> > > P F Grand Total
> > > A 10 3 13
> > > B 15 8 23
> > > C 5 4 9
> > > Grand Total 30 15 45
> > >
> > > A1 = [Count of results]
> > >
> > > i am using the GetPivotData function to retrieve the grand totals but i can
> > > only
> > > retrieve the row grand totals (13, 23, 9) and not the column grand totals
> > > (30, 15, 45).
> > > Here is what i tried:
> > > =GETPIVOTDATA(A1, "A") --> 13
> > > =GETPIVOTDATA(A1, "Result") --> 45
> > > =GETPIVOTDATA(A1, "P") --> #N/A
> > > =GETPIVOTDATA(A1, "F") --> #N/A
> > >
> > > I have tried using cell references but it doesn't make any difference.
> > > I read many different how-tos about this function but the only solution i
> > > found assumed the presence of column sub-totals.
> > >
> > > What am i doing wrong? Help is appreciated
> > >
> > > --
> > > Thanks in advance
> > > /Enrico

The data field is calculated using the COUNT function and both column and row
grand totals are displayed. I also grouped some related rows together and
enabled drill-down.

After reading your suggestion I experimented with different column headings
and I found out that the formula works perfectly with some pivot item values
while I get #N/A with others. For example:

=GETPIVOTDATA(A1, "Result P") = Always #N/A
=GETPIVOTDATA(A1, "Result Passed") = Works
=GETPIVOTDATA(A1, "Result C") = Works
=GETPIVOTDATA(A1, "Result I") = Works
=GETPIVOTDATA(A1, "Result N") = Sometimes works, sometimes #N/A, apparently
depending on the headings of the other columns.

It seems that the formula has troubles handling 1-char item values or maybe
only some particular values. What do you think?
Anyway my solution is to rename the pivot items in the column to a word
instead of a letter and everything should work fine. I will report here if I
encounter any problems.

Thanks a lot for your help

--
/Enrico


"K Dales" wrote:

> I am using Excel 2000 and the formula I gave works for me regardless of how
> many values are in the column. The results are dependent on your table setup
> so I would need to know the entire layout. For the values included in the
> table, did you choose Sum, Count, Max, Min...? Are the column grand totals
> displayed? What is the text showing in the very upper left cell of the table
> (e.g. "Sum of Value")? The key to using the formula GETPIVOTDATA is to
> exactly match the column headings and row labels from your table - and the
> results depend on the function used to summarize your data (for example, are
> you showing the Sum of your numbers, or a count, or the min, max, ...?).
>
> --
> - K Dales
>
>
> "Enrico Campidoglio" wrote:
>
> > I tried both:
> > =GETPIVOTDATA(A1, "Result P")
> > =GETPIVOTDATA(A1, "Grand Total P")
> >
> > but I still get #N/A. I noticed though that the above works when there is
> > only 1 value in the result column i specify in the formula (either P or F).
> >
> > It seems to me that this formula's behaviour is not really stable, at least
> > not in Excel 2000 which I am using right now. I read about other users
> > reporting problems with it.
> >
> > The reason I started to use it in the first place was to retrieve the totals
> > from the pivot table into another worksheet. First I thought about simply
> > doing it in VBA but then I was suggested to use this formula which looked
> > like a much better solution to me. Anyway I sure wasn't expecting it to be so
> > complicated...
> >
> > Do you have any suggestions?
> >
> > --
> > Thanks in advance
> > /Enrico
> >
> >
> > "Jim Thomlinson" wrote:
> >
> > > Try this. GetPivot Data can refenece more than one dimension iun the second
> > > argument...
> > >
> > > =GETPIVOTDATA(A1, "Result F")
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "Enrico Campidoglio" wrote:
> > >
> > > > Hi!
> > > >
> > > > I have a pivot table with the following layout:
> > > > [Count of results]
> > > > [Status] [Result]
> > > > P F Grand Total
> > > > A 10 3 13
> > > > B 15 8 23
> > > > C 5 4 9
> > > > Grand Total 30 15 45
> > > >
> > > > A1 = [Count of results]
> > > >
> > > > i am using the GetPivotData function to retrieve the grand totals but i can
> > > > only
> > > > retrieve the row grand totals (13, 23, 9) and not the column grand totals
> > > > (30, 15, 45).
> > > > Here is what i tried:
> > > > =GETPIVOTDATA(A1, "A") --> 13
> > > > =GETPIVOTDATA(A1, "Result") --> 45
> > > > =GETPIVOTDATA(A1, "P") --> #N/A
> > > > =GETPIVOTDATA(A1, "F") --> #N/A
> > > >
> > > > I have tried using cell references but it doesn't make any difference.
> > > > I read many different how-tos about this function but the only solution i
> > > > found assumed the presence of column sub-totals.
> > > >
> > > > What am i doing wrong? Help is appreciated
> > > >
> > > > --
> > > > Thanks in advance
> > > > /Enrico