At some expense in speed, you could refer to the two ranges as
offset('Analysis Working'!$Y$2,0,0,2099,1) and offset('Analysis
Working'!$C$2,0,0,2099,1)

"Judy" wrote:

Hi,

I need to make the following formula "completely" absolute in it's cell
references.

=SUMPRODUCT(('Analysis Working'!$Y$2:$Y$2100="A")*('Analysis
Working'!$C$2:$C$2100="Accounting & Legal"))

I am currently running a macro on the sheet it refers to that deletes lines.
Each time I run it the formula shanges the range. I have tried whole
columns and found that doesn't work with SUMPRODUCT, nor does INDIRECT.

Any suggestions would be greatly appreciated.

Thanks
Judy