Forum Discussion

ECJ53's avatar
ECJ53
Copper Contributor
Jul 14, 2020
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 ...
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Jul 14, 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's avatar
ECJ53
Copper Contributor
Jul 14, 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

EJ

 

 

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jul 14, 2020

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.

 

 

  • ECJ53's avatar
    ECJ53
    Copper Contributor
    Jul 15, 2020

    Riny_van_Eekelen 

     

    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

Resources