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

Perhaps you could encode the formula within the macro so that it will
always cover up to row 2100, and then just re-instate it after you
delete those rows.

Alternatively, if you count how many rows you delete, (lets say 6, so
that the end range is 2094) then within the macro you could do a
search/replace and change $2094 back to $2100.

Hope this helps.

Pete

On Jul 4, 1:34*am, 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

On Jul 3, 8:34*pm, 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

could try entering as an array formula referencing entire columns.
=SUM( ('Analysis Working'!Y:Y = "A")*('Analysis Working'!C:C =
"Accounting & Legal") )
(ctrl-shift-enter)

On Jul 3, 8:51*pm, recrit wrote:
On Jul 3, 8:34*pm, 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

could try entering as an array formula referencing entire columns.
=SUM( ('Analysis Working'!Y:Y = "A")*('Analysis Working'!C:C =
"Accounting & Legal") )
(ctrl-shift-enter)

correction:
SUM( (if('Analysis Working'!Y:Y = "A", 1, 0)*(if('Analysis Working'!
C:C ="Accounting & Legal"), 1, 0) )

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

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

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Judy" wrote in message
...
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