Forum Discussion
Using ISERROR to remove #NA, SUM doesn't give correct results
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)
ECJ53 , ISERROR with a 0 should work. Is the $0 entered in quotes and seen as a text? Can you upload your file (removing any confidential information)?
Yes, I have attached a copy of the spreadsheet for you to review.
Thank you for your assistance
Elliot
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)Thank you, that was the issue.
One question? This spreadsheet was only a few lines but if I had a spreadsheet with a hundred rows or more, how would you copy the formula down the column and NOT pick up the additional rows?
I thought I had dragged it in the Total mode, not in the full worksheet mode?
Thanks
EJ
With practically same idea as above I'd simplify VLOOKUP formula as
=IFNA(VLOOKUP(A5,Data_Set,8,FALSE),0)and sum totals as
=SUMIFS($G$2:$G$39,$A$2:$A$39,"*Total")
