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 ...

Formulas and Functions

Riny_van_Eekelen
Jul 15, 2020
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

Copper Contributor

Jul 15, 2020

Riny_van_Eekelen

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