Jul 14 2020 04:42 PM
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
Jul 14 2020 04:52 PM
Jul 14 2020 04:54 PM
Jul 14 2020 04:55 PM
@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)?
Jul 14 2020 08:46 PM
Yes, I have attached a copy of the spreadsheet for you to review.
Thank you for your assistance
Elliot
Jul 14 2020 09:22 PM - edited Jul 14 2020 09:23 PM
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)
Jul 14 2020 09:47 PM
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
Jul 14 2020 10:08 PM
@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.
Jul 14 2020 10:14 PM
@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.
Jul 14 2020 10:43 PM
Jul 15 2020 04:16 AM
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")
Jul 15 2020 07:42 AM
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
Jul 14 2020 09:22 PM - edited Jul 14 2020 09:23 PM
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)