Forum Discussion
Solved
Using ISERROR to remove #NA, SUM doesn't give correct results
I am using VLOOKUP to pull information from one worksheet to another. The formula works, but because there are several #NA, which won't allow me to sum the column, I used ISERROR to replace the #NA ...
ECJ53 Since you dragged the VLOOKUP function down in column G, you also pick-up values for the rows that are NOT sub-totals, and you are thus double counting several entries (demonstrated in the picture below), explaining a sum higher than expected. Remove the amounts on all rows that are not sub-totals and the result will be correct.
With regard to the formula itself, you may want to use this one in stead:
=IFERROR(VLOOKUP(A5,Data_Set,8,FALSE),0)
I'm guessing your vlookup is returning numbers that are actually text.
I would return a 0 with your ISNA function. Then, try summing the cells using:
=Sumproduct(--(A1:A10))
Or, you could take a look at the source data and confirm that it is actually numeric using ISNUMBER and/or ISTEXT functions.
I would return a 0 with your ISNA function. Then, try summing the cells using:
=Sumproduct(--(A1:A10))
Or, you could take a look at the source data and confirm that it is actually numeric using ISNUMBER and/or ISTEXT functions.