Forum Discussion
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 with a blank cell (""). The problem is when I try to sum the column it gives me the wrong result. I have also tried using $0 instead of the ("") and I have the same problem.
The only solution I have found is to add the results using "+" and this works for this sheet because there are only 6 entries. But I have another worksheet that has over 100 entries and that would be much more difficult to add them up manually.
Does anyone know the solution to this?
Thanks
EJ
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)
11 Replies
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)
- The sum function will ignore text, even if it looks like a number. But, when you tell excel to perform an explicit mathematical operation (like A1+A2), then it will attempt to convert the text to a number in the background.
- 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.
