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

     

     

     

     

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jul 15, 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