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

March 2017




Issue


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!

Solution

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

Note


Solved by rizvisa1

Related


Published 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 (ccm.net).