Using ISERROR to remove #NA, SUM doesn't give correct results

[ECJ53]

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 with a blank cell ("").  The problem is when I try to sum the column it gives me the wrong result.  I have also tried using $0 instead of the ("") and I have the same problem.

The only solution I have found is to add the results using "+" and this works for this sheet because there are only 6 entries.  But I have another worksheet that has over 100 entries and that would be much more difficult to add them up manually.

Does anyone know the solution to this?

Thanks

EJ

[#2]

I'm guessing your vlookup is returning numbers that are actually text.

I would return a 0 with your ISNA function. Then, try summing the cells using:

=Sumproduct(--(A1:A10))

Or, you could take a look at the source data and confirm that it is actually numeric using ISNUMBER and/or ISTEXT functions.

[#3]

The sum function will ignore text, even if it looks like a number. But, when you tell excel to perform an explicit mathematical operation (like A1+A2), then it will attempt to convert the text to a number in the background.

[#4]

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

[#5]

@TheAntony 

Yes, I have attached a copy of the spreadsheet for you to review.

Thank you for your assistance 

Elliot

[#6 · Accepted solution]

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

[#7]

@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

[#8]

@ECJ53 , You can use this formula :

=IFERROR(IF(RIGHT(A2,5)="Total",VLOOKUP(A2,Data_Set,8,FALSE),0),0)

Basically, it checks it Item in col A ends with a total and does the lookup. You can drag the formula down if you have more rows. See attached.

[#9]

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

 

[#10]

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

[#11]

@ECJ53 

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

[#12]

@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