Previous Topic Next Topic

Formula to replace "#N/A" with 0

[Solved/Closed]
Report
-
 SS -
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

Answer 1 / 1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
you have to use iserror


=IF(ISERROR(VLOOKUP(..), 0, VLOOKUP(...))
2
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Report

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.
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
Why would you need to use vlookup ? Why not use SUMIF for adding values
Report

cause i'm a dummy! Thanks so much!
Ask a question