Excel - A formula to replace "#N/A" with 0

April 2018


I have a worksheet that references another sheet and pulls in a date and a transaction amount (simplified). Sometimes the results of my cells are #N/A (I understand why). I want to have another sheet where I'm essentially using a vlookup to find a specific date and return the transaction amount for that date.

I want to then sum all the transactions that have occurred since this vlookup date. I have 2 problems. Sometimes I have two transactions keyed on the same date, which messes up my vlookup (I'd like suggestions here too, but have a work around for this), but my bigger issue is that when the vlookup returns a date that is associated with a "#N/A" cell. Once this happens, I'm no longer to sum the total of my transactions. I'd appreciate any help! Thanks!


  • You have to use the IsError function.
    • =IF(ISERROR(VLOOKUP(..), 0, VLOOKUP(...)) 


Solved by rizvisa1
Published by aakai1056. Latest update on September 6, 2011 at 09:08 AM by aakai1056.
This document, titled "Excel - A formula to replace "#N/A" with 0," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (https://ccm.net/).
Importing an Excel table into Access
How to use text value in cell as criteria