SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1522291%22%20slang%3D%22en-US%22%3EUsing%20ISERROR%20to%20remove%20%23NA%2C%20SUM%20doesn't%20give%20correct%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1522291%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20VLOOKUP%20to%20pull%20information%20from%20one%20worksheet%20to%20another.%3C%2FP%3E%3CP%3EThe%20formula%20works%2C%20but%20because%20there%20are%20several%20%23NA%2C%20which%20won't%20allow%20me%20to%20sum%20the%20column%2C%20I%20used%20ISERROR%20to%20replace%20the%20%23NA%20with%20a%20blank%20cell%20(%22%22).%26nbsp%3B%20The%20problem%20is%20when%20I%20try%20to%20sum%20the%20column%20it%20gives%20me%20the%20wrong%20result.%26nbsp%3B%20I%20have%20also%20tried%20using%20%240%20instead%20of%20the%20(%22%22)%20and%20I%20have%20the%20same%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20only%20solution%20I%20have%20found%20is%20to%20add%20the%20results%20using%20%22%2B%22%20and%20this%20works%20for%20this%20sheet%20because%20there%20are%20only%206%20entries.%26nbsp%3B%20But%20I%20have%20another%20worksheet%20that%20has%20over%20100%20entries%20and%20that%20would%20be%20much%20more%20difficult%20to%20add%20them%20up%20manually.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20know%20the%20solution%20to%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEJ%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1522291%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1522306%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20ISERROR%20to%20remove%20%23NA%2C%20SUM%20doesn't%20give%20correct%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1522306%22%20slang%3D%22en-US%22%3EI'm%20guessing%20your%20vlookup%20is%20returning%20numbers%20that%20are%20actually%20text.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20would%20return%20a%200%20with%20your%20ISNA%20function.%20Then%2C%20try%20summing%20the%20cells%20using%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DSumproduct(--(A1%3AA10))%3CBR%20%2F%3E%3CBR%20%2F%3EOr%2C%20you%20could%20take%20a%20look%20at%20the%20source%20data%20and%20confirm%20that%20it%20is%20actually%20numeric%20using%20ISNUMBER%20and%2For%20ISTEXT%20functions.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1522307%22%20slang%3D%22en-US%22%3ERE%3A%20Using%20ISERROR%20to%20remove%20%23NA%2C%20SUM%20doesn't%20give%20correct%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1522307%22%20slang%3D%22en-US%22%3EThe%20sum%20function%20will%20ignore%20text%2C%20even%20if%20it%20looks%20like%20a%20number.%20But%2C%20when%20you%20tell%20excel%20to%20perform%20an%20explicit%20mathematical%20operation%20(like%20A1%2BA2)%2C%20then%20it%20will%20attempt%20to%20convert%20the%20text%20to%20a%20number%20in%20the%20background.%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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

11 Replies
Highlighted
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.
Highlighted
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.
Highlighted

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

Highlighted

@TheAntony 

 

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

 

Thank you for your assistance 

 

Elliot

 

 

Highlighted
Best Response confirmed by ECJ53 (Occasional Contributor)
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.

Screenshot 2020-07-15 at 06.09.48.png

With regard to the formula itself, you may want to use this one in stead:

 

=IFERROR(VLOOKUP(A5,Data_Set,8,FALSE),0)

 

 

 

 

Highlighted

@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

 

 

Highlighted

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

Highlighted

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

Screenshot 2020-07-15 at 07.12.36.png

Screenshot 2020-07-15 at 07.11.35.png

 

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

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

@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