+ Reply to Thread
Results 1 to 15 of 15

Sum of values in date range returned via GETPIVOTDATA causes #REF errors

  1. #1
    Registered User
    Join Date
    06-05-2014
    Posts
    7

    Sum of values in date range returned via GETPIVOTDATA causes #REF errors

    Hey guys,

    I have a Pivot Table for a report that is updated daily when new data is uploaded to my company's intranet website. I want to have a running total for the month thusfar. To do this, I'm using GETPIVOTDATA to return the sum of values in a given date range. This way, I can just refresh the Pivot Table daily, and all the new numbers populate.

    This formula looks like this:

    \1

    Right now, it works because I have the data for the whole range of dates I'm referencing in the Pivot Table (almost all of May).

    For June, it returns a REF error because those dates currently do not have data.

    \1

    How can I have Excel ignore the #REF errors and calculate the sum normally? This will help me calculate a running total for the current month and make everything much easier for me (it'll make these reports a simple refresh).

    Thanks for any help, I appreciate it.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Sum of values in date range returned via GETPIVOTDATA causes #REF errors

    Use the option running totals in the pivot table.

    Since you don't add an excel file, I can't show it to you.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    06-05-2014
    Posts
    7

    Re: Sum of values in date range returned via GETPIVOTDATA causes #REF errors

    Quote Originally Posted by oeldere View Post
    Use the option running totals in the pivot table.

    Since you don't add an excel file, I can't show it to you.
    Do you mean via a Filter? The Pivot Table just stores the information I grab using GETPIVOTDATA. The reason why I setup the report this way is that I can easily, with a small reference change, change the report to another element instead of the current one. If I use a filter, won't I have to update that manually? And finally, my company's "months" are odd, some days are considered part of the previous or next month, depending on how the days actually fall so I can't filter the data using Excel's month filters i.e. May is considered 05/04/2014-05/31/2014.

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Sum of values in date range returned via GETPIVOTDATA causes #REF errors

    No I mean the running total in the pivot table.

  5. #5
    Registered User
    Join Date
    06-05-2014
    Posts
    7

    Re: Sum of values in date range returned via GETPIVOTDATA causes #REF errors

    Quote Originally Posted by oeldere View Post
    No I mean the running total in the pivot table.
    I looked up using Running Totals in the Pivot Table but it isn't something I want to use because it means that I'll have to filter out dates manually that I don't want to include in the report.

    I've researched other fixes to this problem and I see suggestions like this:http://answers.microsoft.com/en-us/o...6-52ad3e0aeec7 but I'm not knowledgeable enough to apply that "fix" to the array formula in the first post; all I really need to solve this problem is to get that working.

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Sum of values in date range returned via GETPIVOTDATA causes #REF errors

    I posted this line in my solution in #2

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    06-05-2014
    Posts
    7

    Re: Sum of values in date range returned via GETPIVOTDATA causes #REF errors

    Quote Originally Posted by oeldere View Post
    I posted this line in my solution in #2

    Please Login or Register  to view this content.
    Can't upload my file, the attachments page does not allow me to (it shows a red exclamation point and on hover, it says io error).

    I can't upload the file to most file sharing websites because those websites are blocked on this network. The best I can do is Dropbox: https://dl.dropboxusercontent.com/u/...5/example.xlsx

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Sum of values in date range returned via GETPIVOTDATA causes #REF errors

    See the attached file with an running total.

    If you add new data in your table, it will also be added in the pivot table.

    If you add new data to your table you always have to refresh your pivot table.

    Excel 2007 => data => refresh (pivot table)
    Attached Files Attached Files

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Sum of values in date range returned via GETPIVOTDATA causes #REF errors

    An introduction to Running Totals is at this site:

    http://blog.contextures.com/archives...-pivot-tables/
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  10. #10
    Registered User
    Join Date
    06-05-2014
    Posts
    7

    Re: Sum of values in date range returned via GETPIVOTDATA causes #REF errors

    Quote Originally Posted by oeldere View Post
    See the attached file with an running total.

    If you add new data in your table, it will also be added in the pivot table.

    If you add new data to your table you always have to refresh your pivot table.

    Excel 2007 => data => refresh (pivot table)
    Quote Originally Posted by newdoverman View Post
    An introduction to Running Totals is at this site:

    http://blog.contextures.com/archives...-pivot-tables/
    Quote Originally Posted by 01111000 View Post
    I looked up using Running Totals in the Pivot Table but it isn't something I want to use because it means that I'll have to filter out dates manually that I don't want to include in the report.

    I've researched other fixes to this problem and I see suggestions like this:http://answers.microsoft.com/en-us/o...6-52ad3e0aeec7 but I'm not knowledgeable enough to apply that "fix" to the array formula in the first post; all I really need to solve this problem is to get that working.
    Thanks for the help, but is there way way to get the running total for a specific range of dates I specify without having to modify the pivot table (other than a daily refresh)? The link in the above post is exactly what I want to do, I just need to get that to work with my GetPivotData.

  11. #11
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Sum of values in date range returned via GETPIVOTDATA causes #REF errors

    maybe
    =sumproduct(iferror(getpivotdataformula,0))
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  12. #12
    Registered User
    Join Date
    06-05-2014
    Posts
    7

    Re: Sum of values in date range returned via GETPIVOTDATA causes #REF errors

    Quote Originally Posted by JosephP View Post
    maybe
    =sumproduct(iferror(getpivotdataformula,0))
    Tried adapting it, it doesn't work. Thanks though

  13. #13
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Sum of values in date range returned via GETPIVOTDATA causes #REF errors

    double post
    Last edited by JosephP; 06-11-2014 at 11:35 AM.

  14. #14
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Sum of values in date range returned via GETPIVOTDATA causes #REF errors

    my bad-for your formula it oughta be like this

    =SUM(IFERROR(GETPIVOTDATA("Element",'Daily'!$A$1,"DATE",ROW(INDIRECT(L4&":"&L5)),"C","DC"),0))
    and you have to enter it with ctrl+shift+enter because it's an array formula. if it doesn't work you'll have to show us the actual formula you tried

  15. #15
    Registered User
    Join Date
    06-05-2014
    Posts
    7

    Re: Sum of values in date range returned via GETPIVOTDATA causes #REF errors

    Quote Originally Posted by JosephP View Post
    my bad-for your formula it oughta be like this

    =SUM(IFERROR(GETPIVOTDATA("Element",'Daily'!$A$1,"DATE",ROW(INDIRECT(L4&":"&L5)),"C","DC"),0))
    and you have to enter it with ctrl+shift+enter because it's an array formula. if it doesn't work you'll have to show us the actual formula you tried
    Thank you! That did it. Here's the updated file with the working formula: https://dl.dropboxusercontent.com/u/...5/example.xlsx

    Can you please download it and reupload it here? I'd like it to be archived in case someone needs the solution to a similar problem; I can't get the uploader to work on my work's intranet.

    Thanks again JosephP, this will make many reports easier for me in the future.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Date Range need to have a specific value returned
    By pingwn33 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-16-2014, 12:39 PM
  2. [SOLVED] Getpivotdata for a date range criteria
    By kanonathena in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 09-18-2013, 08:46 PM
  3. [SOLVED] [B] VLOOKUP where range is values returned from another formula [/B]
    By alma1219 in forum Excel General
    Replies: 1
    Last Post: 07-04-2012, 06:59 AM
  4. Replies: 2
    Last Post: 09-12-2011, 05:24 PM
  5. [SOLVED] Handling #REF returned from GETPIVOTDATA
    By Laurence Lombard in forum Excel General
    Replies: 1
    Last Post: 05-10-2005, 08:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1