Hi Everybody

If I have 2 cells, say A1 and A2 on a worksheet in a workbook. Neither, either or both of which can contain error values. In other words, none of the two, one of two or both of them can contain error values.

I now want to add the values of cells A1 and A2 in another cell say A3 - easy enough!

The problem lies here :-

I have a very large spreadsheet with huge number of cells with existing formula with different parameters for months, states etc. etc - the question hence of manually changing the existing formula to include an "if" condition is not a viable solution.

What I want to know is if there is a function (or a combination of functions) that I can use that will perform something similar to the "sum()" function but will ignore those arguments that result in an error value - I should be able to globally replace "this with that" in the selected range!

For example, the following formula is an example of the existing formula array entered :-

=IF((--(ISERROR(INDEX(Index_200407_NSW,MATCH(TRIM($A10),TRIM(MatchCol_200407_NSW),0),1)))+--(ISERROR(INDEX(Index_200406_NSW,MATCH(TRIM($A10),TRIM(MatchCol_200406_NSW),0),1)))),"",(INDEX(Index_200407_NSW,MATCH(TRIM($A10),TRIM(MatchCol_200407_NSW),0),1)+INDEX(Index_200406_NSW,MATCH(TRIM($A10),TRIM(MatchCol_200406_NSW),0),1)))

This can be simplified like so :-


=IF((--(ISERROR(A1))+--(ISERROR(A2))) = 2,"", somecombofunction(A1, A2))

The "somecombofunction" should ignore A1 or A2 if they contain error values resulting in zero in both A1 and A2 contain error values!


Any suggestions please!!!!!!!!!


Best regards



Deepak Agarwal