To shorten the formula

I think it's fair to summarise (please correct me, of course, I can't see the raw data...)

=if( iserror( <complex lookup 1> ), 0, <complex lookup 1> ) + if( iserror( <complex lookup 2> ), 0, <complex lookup 2> )

You could split the formulae like so:
column 1 = <complex lookup 1>
column 2 = <complex lookup 2>
column 3 = if( iserror( <column 1 value> ), 0, <column 1 value> ) + if( iserror( <column 2 value> ), 0, <column 2 value> )

This has the added benefit of making it easier to edit the formulae as each lookup is only written once (and so only needs editing once if you need to change it).

You can easily hide the preceding columns.

HTH