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.