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)
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")- Copy the cell containing your formula (Ctrl+C). Then, select your destination range. On the home tab, click the find and select button in the editing group (far right). In the dialog box select "visible cells only." Then paste (Ctrl+V).
ECJ53 I put an auto filter on the table, then filter out all rows where column A does not contain "Total". select the displayed cells in G and clear them.
Thank you for the additional information. This is a report that I will have to prepare occasionally, and at least once a year, and I will save all of the information provided by all of the respondents to make the work easier.
Thanks
EJ
