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.

Originally Posted by oeldere

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.

Originally Posted by oeldere

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.

Originally Posted by oeldere

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

Originally Posted by oeldere

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)

Originally Posted by newdoverman

An introduction to Running Totals is at this site:

http://blog.contextures.com/archives...-pivot-tables/

Originally Posted by 01111000

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.

Originally Posted by JosephP

maybe
=sumproduct(iferror(getpivotdataformula,0))

Tried adapting it, it doesn't work. Thanks though

Originally Posted by JosephP

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.