How to get a default of 0 instead of #REF!

I have an Excel spreadsheet with several pivot table
Then I have a sheet which collects data from the pivit table
The issue I have is that if the value does not appear on the pivot table I get "
#REF! and then it will not give me a total
the formula is
=+GETPIVOTDATA("Total Hrs Cost",'By Month'!$A$1,"Month","MAR")

Can I modify this so that if the value does not appear on the pivot table it defaults to 0
Then I should be able to total the values

Gordon
Gordon HughesDirectorAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
Gordon

Which version of Excel (2007 or later I think are you on?

If it's a later version you can use IFERROR.

=IFERROR(GETPIVOTDATA("Total Hrs Cost",'By Month'!$A$1,"Month","MAR"),0)

For earlier version you could use ISERROR.

=IF(ISERROR(GETPIVOTDATA("Total Hrs Cost",'By Month'!$A$1,"Month","MAR")), 0, GETPIVOTDATA("Total Hrs Cost",'By Month'!$A$1,"Month","MAR"))
0
 
Hello ThereSystem AdministratorCommented:
It happens when cells that were referenced by formulas were deleted.
https://support.office.com/en-us/article/how-to-correct-a-ref-error-822c8e46-e610-4d02-bf29-ec4b8c5ff4be
0
 
Gordon HughesDirectorAuthor Commented:
Works ok and quick response
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.