Help with ISNA

[agamemnian1]

Hi all

I'm not great with Excel so would appreciate any help you may be able to offer.

I have a workbook with data on 7 different worksheets. I am trying to do a single formula to look do a vlookup across all 7 worksheets at the same time and then sum all of those values together.

Now on worksheets where the lookup value exists this works fine. However on a worksheet where the lookup value doesnt exist this returns an #NA error and this in turn breaks the formula as it can;t sum an NA value.

The working formula is.

=(SUM(VLOOKUP($A2,'Cases Opened'!A:H,2,FALSE),VLOOKUP($A2,'Cases Closed'!A:J,2,FALSE),VLOOKUP($A2,'Cases Resolved'!A:K,2,FALSE),VLOOKUP($A2,'Emails Added'!A:H,2,FALSE),VLOOKUP($A2,'Test Results'!A:H,2,FALSE),VLOOKUP($A2,'Case Comments'!A:J,2,FALSE)))

Reading up it seems an isna or is error function may be of use here so I've tried this but it wont accept the forumala i'm putting in.

=(if(isna(SUM(VLOOKUP($A2,'Cases Opened'!A:H,2,FALSE),VLOOKUP($A2,'Cases Closed'!A:J,2,FALSE),VLOOKUP($A2,'Cases Resolved'!A:K,2,FALSE),VLOOKUP($A2,'Emails Added'!A:H,2,FALSE),VLOOKUP($A2,'Test Results'!A:H,2,FALSE),VLOOKUP($A2,'Case Comments'!A:J,2,FALSE))0)))

Can anybody help me out with where Im going wrong?

Would be most appreciative of any help.

[Glenn Kennedy]

Iferror(your formula,"") will do the job

[agamemnian1]

Thanks for the quick response Glenn.

That does make the formula work though its not quite what I was looking for.

The lookup values exist in 5 of the 7 worksheets so I need to sum these together.

Doing the below returns a value of 0 for the overall sum total. What I'm trying to achieve is if the lookup value doesnt exist on one of the worksheets its performing a vlookup on to return a 0 for that vlookup rather than an NA so that it will sum together all of the different vlookups.

=IFERROR(SUM(VLOOKUP($A3,'Cases Opened'!A:H,2,FALSE),VLOOKUP($A3,'Cases Closed'!A:J,2,FALSE),VLOOKUP($A3,'Cases Resolved'!A:K,2,FALSE),VLOOKUP($A3,'Emails Added'!A:H,2,FALSE),VLOOKUP($A3,'Test Results'!A:H,2,FALSE),VLOOKUP($A3,'Case Comments'!A:J,2,FALSE)),"0")

Is there a way to do this so it will return a 0 just for a specific vlookup rather than displaying a 0 for the whole sum as thats not quite right?

[Glenn Kennedy]

OK. One way would be to wrap each of the vlookups in an error catching formula:

=sum(iferror((vlookup1),0),iferror((vlookup2),0),iferror((vlookup3),0))