How to perform SUM of multiple VLOOKUP values by ignoring N/A values (or treating them as 0)?

[thisisit]

Hi all

I am trying to SUM multiple vlookup values as below:

=VLOOKUP(A3,Academy!A:M,13,FALSE)+VLOOKUP(A3,'AJ Blunt'!A:M,13,FALSE)+VLOOKUP(A3,BUILT!A:M,13,FALSE)+VLOOKUP(A3,Carbridge!A:M,13,FALSE)+VLOOKUP(A3,'Carter Corp'!A:M,13,FALSE)+VLOOKUP(A3,'Cleanaway - Transpacific'!A:M,13,FALSE)

Column A contains dates, and not all worksheets contain all dates, meaning that vlookup sometimes returns N/A value which in turns returns a N/A value overall.

How do I perform a SUM of all my individual lookup values so that N/A values are treated as 0?

 

[Sanjeev1976]

You can use vlookup with iferror

=IFERROR(VLOOKUP(A3,Academy!A:M,13,FALSE),0)+IFERROR(VLOOKUP(A3,'AJ Blunt'!A:M,13,FALSE),0)+IFERROR(VLOOKUP(A3,BUILT!A:M,13,FALSE),0)+IFERROR(VLOOKUP(A3,Carbridge!A:M,13,FALSE),0)+IFERROR(VLOOKUP(A3,'Carter Corp'!A:M,13,FALSE),0)+IFERROR(VLOOKUP(A3,'Cleanaway - Transpacific'!A:M,13,FALSE),0)