Sumproduct absolute cell references

[Judy]

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

 

[Pete_UK]

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

 

[recrit]

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)

 

[recrit]

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) )

 

[bpeltzer]

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)

 

[Bob Phillips]

=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)