Problems using autosum when VLOOKUP produces #N/A

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a prepopulated order form which uses VLOOKUPS. Until the user
completes a line, VLOOKUP produces a not available result #N/A. I would like
to include an order total field which gives the user a constantly updated
order total, however the #N/A is preventing me from doing so. As long as
there is a #N/A in the colum, the sum field only shows #N/A

Is there a way to get the sum to work?
THanks
 
If you're typing the value to look up in column A, maybe you could use:

=if(a2="","",vlookup(a2,.....))

=sum() will ignore text values--including "".
 
You could also use Iserror in combination with If statement to work around
this problem.... for example if your vlookup is like
=vlookup(a2,page!A2:G15,3,false).... then just add
if(iserror(vlookup(a2,page!A2:G15,3,false))=true,0,vlookup(a2,page!A2:G15,3,false)).
This function will get rid of any errors that appear in the vlookup function.
I hope this helps.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Vlookup producing "#n/a" 1
Remove #n/a in vlookup to sum results 6
VLOOKUP - 0 instead of # N/A 8
IF/VLOOKUP with #N/A results 6
If condition not met get #N/A 5
Hiding VLookup #N/A 1
Sum Function When Formula Produces #N/A 2
VLOOKUP returning #N/A result 2

Back
Top