Formula to replace "#N/A" with 0

Solved/Closed
SS - Jul 14, 2010 at 07:14 PM
 SS - Jul 20, 2010 at 09:24 PM
Hello,
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


1 reply

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jul 14, 2010 at 07:26 PM
you have to use iserror


=IF(ISERROR(VLOOKUP(..), 0, VLOOKUP(...))
2
Thanks! will mark as solved but wanted to see if you had suggestion for my other issue since that was such an easy fix. Using formula above, sometimes I have the same date listed multiple times on my table that i'm looking up and the vlookup function will only return the transaction associated with teh first instance of the date. For eg:

I want to add all transaction between 7/10 and 7/12
7/10 - 100
7/11 - 0
7/11 - 100
7/12 - 100

as my vlookup pulls in each date, I only get 200 since it just pulls in the $0 for 7/11.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jul 18, 2010 at 09:20 AM
Why would you need to use vlookup ? Why not use SUMIF for adding values
0
cause i'm a dummy! Thanks so much!
0