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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Thanks in advance, Monkdelafunk

Perhaps

Please Login or Register  to view this content.

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