Formula to calculate result whilst avoiding errors is too long!

Konnichi wa comrades,

I have a formula I've been using for a long time which uses VLOOKUP to find results based on various reference cells, and then adds them up. To avoid errors caused by VLOOKUP not finding anything for one of the references I have also used ISERROR. The formula returns a blank if the calculation returns a zero.

I now need to bring further references in to the calculation but, using the format I have been, the formula is now too long. Here is my extensive formula:

 =IF((SUM((IF(ISERROR(VLOOKUP($K6,'1st'!$B$4:$Q$340,16,)),"0",(VLOOKUP($K6,'1st'!$B$4:$Q$340,16,FALSE)))))+((IF(ISERROR(VLOOKUP($L6,'1st'!$B$4:$Q$340,16,)),"0",(VLOOKUP($L6,'1st'!$B$4:$Q$340,16,FALSE)))))+((IF(ISERROR(VLOOKUP($M6,'1st'!$B$4:$Q$340,16,)),"0",(VLOOKUP($M6,'1st'!$B$4:$Q$340,16,FALSE)))))+((IF(ISERROR(VLOOKUP($N6,'1st'!$B$4:$Q$340,16,)),"0",(VLOOKUP($N6,'1st'!$B$4:$Q$340,16,FALSE)))))((IF(ISERROR(VLOOKUP($O6,'1st'!$B$4:$Q$340,16,)),"0",(VLOOKUP($O6,'1st'!$B$4:$Q$340,16,FALSE)))))+((IF(ISERROR(VLOOKUP($P6,'1st'!$B$4:$Q$340,16,)),"0",(VLOOKUP($P6,'1st'!$B$4:$Q$340,16,FALSE))))))=0,"",(SUM((IF(ISERROR(VLOOKUP($K6,'1st'!$B$4:$Q$340,16,)),"0",(VLOOKUP($K6,'1st'!$B$4:$Q$340,16,FALSE)))))+((IF(ISERROR(VLOOKUP($L6,'1st'!$B$4:$Q$340,16,)),"0",(VLOOKUP($L6,'1st'!$B$4:$Q$340,16,FALSE)))))+((IF(ISERROR(VLOOKUP($M6,'1st'!$B$4:$Q$340,16,)),"0",(VLOOKUP($M6,'1st'!$B$4:$Q$340,16,FALSE)))))+((IF(ISERROR(VLOOKUP($N6,'1st'!$B$4:$Q$340,16,)),"0",(VLOOKUP($N6,'1st'!$B$4:$Q$340,16,FALSE))))))+((IF(ISERROR(VLOOKUP($O6,'1st'!$B$4:$Q$340,16,)),"0",(VLOOKUP($O6,'1st'!$B$4:$Q$340,16,FALSE)))))+((IF(ISERROR(VLOOKUP($P6,'1st'!$B$4:$Q$340,16,)),"0",(VLOOKUP($P6,'1st'!$B$4:$Q$340,16,FALSE))))))

Can anybody help me chop this beast down to size? It may be easier if we look at a version referencing less cells to see if we can shorten this and then factor the changes in to the above. Here is a version which refers to just 2 cells:

=IF((SUM((IF(ISERROR(VLOOKUP($F6,'1st'!$B$4:$Q$340,16,)),"0",(VLOOKUP($F6,'1st'!$B$4:$Q$340,16,FALSE)))))+((IF(ISERROR(VLOOKUP($G6,'1st'!$B$4:$Q$340,16,)),"0",(VLOOKUP($G6,'1st'!$B$4:$Q$340,16,FALSE))))))=0,"",(SUM((IF(ISERROR(VLOOKUP($F6,'1st'!$B$4:$Q$340,16,)),"0",(VLOOKUP($F6,'1st'!$B$4:$Q$340,16,FALSE)))))+((IF(ISERROR(VLOOKUP($G6,'1st'!$B$4:$Q$340,16,)),"0",(VLOOKUP($G6,'1st'!$B$4:$Q$340,16,FALSE)))))))

Thanks in advance, Monkdelafunk

Perhaps

=SUMPRODUCT(SUMIF('1st'!$B$4:$B$340,K6:P6,'1st'!$Q$4:$Q$340))

though the first formula you list is hard to follow I've assumed you're summing col Q on 1st sheet where value in B is one of those values specified in K6:P6

Try this for your second formula.

=SUMPRODUCT((F6='1st'!$B$4:$B$340)+(G6='1st'!$B$4:$B$340),'1st'!$Q$4:$Q$340)

Assumption
If F6 is found in B4:B340 then return the value in Q
If G6 is found in B4:B340 then return the value in Q
sum these two