Pivot Table total to sum over #N/A values

Hi Experts,

I have a pivot table that has balances in it.  The source data has some balances that have #N/A values due to a VLOOKUP.

WIthout modifying the source data, how would I go about modifying the total displayed in the Pivot Table so that it sums everything in a column except for the #N/A values?

Thanks,
rav
rav_ravAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Patrick MatthewsConnect With a Mentor Commented:
Modifyv your vlookup so that it returns 0 instead of #n/a:

=if(isna(vlookup(...)),0,vlookup(...))
0
 
Glenn RayConnect With a Mentor Excel VBA DeveloperCommented:
Create a calculated field in your Pivot Table that checks for #N/A and returns an alternate result.

For example, if the field name in question is "Sales" then create a calculated field called "Sales Value" with the formula:
=if(isna(Sales),0,Sales)

Instead of zero ("0") as the return value above, you should also be able to return "N/A" (text) and still have the values calculated properly in the pivot table.
0
 
rav_ravAuthor Commented:
Sorry for the delayed response folks.
Thanks for your help.
rav
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.