1. J

    I need to replace 0's with NA

    Good morning, I am looking to update a dynamic getpivotdata table so the results can be charted in a bar chart (a pivot table is used to summarise the getpivotdata table so only relevant cells are reflected in the chart). The issue is that the getpivotdata table includes REF errors that impact...
  2. R

    How do sum up multiple items for the same field using cell references in GETPIVOTDATA

    Dear Excel Experts I want to sum up data for more than 2 fields from a pivot table using GETPIVOTDATA formula. When I linked it with one field it is giving correct result as show below =SUM(IFERROR((GETPIVOTDATA("Sum of...
  3. B

    Sum If a Pivot Table Data Set Based on Date

    Hi Gurus, Am I able to sumif a range of dates within a pivot table. The formula I have is as below, but returns a #ref . =GETPIVOTDATA("DUE",$A$3,"Group","AP","Payment Date","<"&DATE(2019, 6, 21)) <tbody> Sum of DUE Column Labels Week Ending AP CX 1/03/2019 10/05/2019...
  4. M

    Dynamic criteria for SUMIF or dynamic getpivotdata

    Hi Everyone, I need help with a spreasheet issue related to my trading strategy. It would be great if I could upload the excel file, but it is too large. So I will carefully explain the problem with several images labelled in a word document -...
  5. C

    Extracting data from Pivot Table

    Hi all I'm trying to automate a monthly report and all the information I need is presented within a pivot table. The pivot table contains two columns, A is Customer, B is value. The pivot table has an additional row within the customer to subdivide them into categories. Essentially i'd like to...
  6. D

    Can I use a condition in GETPIVOTDATA?

    I have a pivot table that is tracking occurrence per device as follows: <colgroup><col><col></colgroup><tbody> PJ1065 1 PJ1089 1 PJ1156 1 PJ1197 1 PJ1573 2 PJ1754 1 PJ3159 1 PJ3223 3 PJ3648 1 PJ3944 1 PJ4064 1 PJ4073 1 PJ4309 1 </tbody> I need to find a way to...
  7. P


    Hello All, GetPivotdata formula will give column values from a Pivot. Is there any formula similar to it which will give row label data from a Pivot?
  8. A

    GETPIVOTDATA conditional grand total

    Dear Excel Community, Currently I have a tab that references data from a pivot table via getpivotdata function. How can I modify the getpivotdata formula in a way that it will give me the grand total value up to a certain month. For example, I would like the grand total from the average price...
  9. A


    Dear Excel Community, Currently I have a tab that references data from a pivot table via getpivotdata function. How can I modify the getpivotdata formula in a way that it will give me the grand total value up to a certain month. For example, I would like the grand total from the average price...
  10. R

    GetPivotData Data Model with Date as Criteria

    Hi - using PP Data model pivot is really new for me, i am trying to use getpivotdata below is the formula =GETPIVOTDATA("[Measures].[Distinct Count of nama]",$K$2,"[Range].[date]","[Range].[date].&[2019-03-12T00:00:00]") i want to get the data based on the date. i tried looking over the...
  11. B

    using getpivotdata as reference point to sum range

    I have a pivot table where my rows are client names an the columns are dates. Is there a way that I can use the getpivotdata function to identify a reference starting point and then sum the prior twelve dates/columns. If there aren't 12 prior dates/column, it would just return zero? I am...
  12. B

    getpivotdata (offset)

    I have the following formula that is returning the correct value: cell 'ODS TTM Coverage'!N12 = GETPIVOTDATA("[Measures].[Amt]",'ODS TTM Coverage'!$A$5,"[Time].[Calendar]","[Time].[Calendar].[Year].&[2017].&[12]&","[Client].[Client.Name].&[M1...
  13. S

    Help with Getpivotdata

    Hey guys, Looking for some assistance as I have never used this formula before. I have multiple accounts with various sellers. I have summarized them into pivot table. What I need is a formula to pull from the pivot table all the names that fall under a certain account. so if account 2 in A2...
  14. K


    Hello I am hoping you can help me. I am trying to use formula GETPIVOTDATA and I can not get it to work! I attached some screen shots below of the pivot and I have created a table by month to bring through the total by month. I cant see what I am doing wrong Any help appreciated Katherine
  15. G


    Hi Would appreciate any help with this GETPIVOTDATA, I need to make it dynamic, and link &[2.018E3] to a year in a cell and&[1.] to week in a cell. I've tried a few things but nothing is working. =GETPIVOTDATA("[Measures].[MP Count]",'[3Data.xlsx]ATT ENT'!$A$4,"[Subscriptions].[Attend...
  16. R

    getpivotdata formula issue that searches for text with an _

    I have an getpivotdata formula that works quiet well for multiple items but fails to return data for one item. The main difference between the item that fails to return a result and the others is that it contains an Underscore. =IFERROR(ROUND(GETPIVOTDATA("Events",Changes!$C$503,"Last changed...
  17. B

    GETPIVOTDATA returns 0 when one item from list missing

    =IFERROR(SUM(GETPIVOTDATA("Count of Age",Pivots!O$3,"Case Type",Cases!$B$4,"Age",{13,14,15,16,17})),0) In my current data set there is nobody age 16 and the formula returns 0 even though there are kids age 13, 14, etc. The formula works fine if I take 16 out of the condition array. But the next...
  18. A

    GETPIVOTDATA Power Pivot

    Hi, I want to make the "getpivotdata" function dynamic. It is important to know that I used Power Pivot to create pivot tables. This is my function: =IFERROR(GETPIVOTDATA("[Measures].["&$D9&"]";'ST...
  19. A

    How to Obtain Bottom Performers from Pivot Table for Excel Dashboard

    Hi All, I need some help with obtaining Pivot Table data. I am unsure about what is the best way to accomplish what I want. I have a Pivot Table that is filtered to display the bottom performers for each class of students. Each class has a different number of bottom performing students. For...
  20. A

    GETPIVOTDATA Relative Reference and Cascading Slicers to Sort Students and Test Scores

    Hi All, I could really use some help before I go jump off a bridge. Jk. The issue I am currently having it related to using relative references in the GETPIVOTDATA function and cascading slicers. I have a PivotTable set up that has a list of students and their test scores. What I would like to...

Some videos you may like

This Week's Hot Topics