DO NOT WANT ROW NUMBER IN FORMULA TO CHANGE

  • Thread starter Thread starter Angie G.
  • Start date Start date
A

Angie G.

Have following formula on cell J1
(=SUMPRODUCT(--($C$3:$C$157>DATE(2008,9,30)),--($C$3:$C$157<DATE(2008,11,1)),$G$3:$G$157)
If I insert a line on row 3, formula changes to $C$4 and $G$4, I want it to
stay as formula is written. Is it possible?
 
Yes.

Try
=SUMPRODUCT(--(INDIRECT("$C$3:$C$157")>DATE(2008,9,30)),--(INDIRECT("$C$3:$C$157")<DATE(2008,11,1)),INDIRECT("$G$3:$G$157"))
 
Thanks, it worked.

Sheeloo said:
Yes.

Try
=SUMPRODUCT(--(INDIRECT("$C$3:$C$157")>DATE(2008,9,30)),--(INDIRECT("$C$3:$C$157")<DATE(2008,11,1)),INDIRECT("$G$3:$G$157"))
Click to expand...
 
INDIRECT evaluates the references as TEXT strings and as such, will never
change when copying the formula or inserting/deleting rows/columns so you
can save a few keystrokes by eliminating the $ signs:

=SUMPRODUCT(--(INDIRECT("C3:C157")>DATE(2008,9,30)),--(INDIRECT("C3:C157")<DATE(2008,11,1)),INDIRECT("G3:G157"))
 
Back
Top