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