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

October 2016


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

Related :

This document entitled « Excel - A formula to replace "#N/A" with 0 » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.