Here is another approach. Modify your formulas like this

=IF(P1="",SUMPRODUCT(--($J$2:$J$31005),--($K$2:$K$3100=1),--($L$2:$L$310051),--($M$2:$M$3100=1),$N$2:$N$3100),"")

then if P1 is anything but blank, the Sumproduct part is not recalculated.
Clear P1 and it recalculates normally. Use any cell you want to control the
calculation.

--
Regards,
Tom Ogilvy


"Carmen" wrote:

I have a very long formula using
sumprodct(--isna(match(.....)),--match(....)). IN this formula, i'm
comparing four columns and these columns are defined in four cells because
they are not constant. The problem with this formula is that it is extremely
slowing down the excel file. Any change made takes a long time to
recalculate, but I can't turn calculation manual because I need other cells
to change.

Now that the formula is set in a cell, can I use a function/macro to convert
the formula to text when this formula is not used and convert it back to
formula when used?

Thanks,

Carmen