I have the following sumproduct formula it is calculating one colomn .
But the problem coming when somebody trying to delete one row inbetween
then inside the formula cell number is decreasing
=SUMPRODUCT((COURIER!B4:B996=TODAY())*(COURIER!D4: D996="S")*(COURIER!C4:C996))
like this. It was B1000 when i made first. any solution for not to
reduce the number.
Secondly i wanted to change the 996 to the end of excel limit
(65536)any short cut to write this.

thanks alot
with regards
nowfal


--
nowfal
------------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=388286

You could use INDIRECT

=SUMPRODUCT(--(INDIRECT("COURIER!B4:B1000")=TODAY()),--(INDIRECT("COURIER!D4
:D1000")="S"),INDIRECT("COURIER!C4:C1000"))

--
HTH

Bob Phillips

"nowfal" wrote in
message ...

I have the following sumproduct formula it is calculating one colomn .
But the problem coming when somebody trying to delete one row inbetween
then inside the formula cell number is decreasing

=SUMPRODUCT((COURIER!B4:B996=TODAY())*(COURIER!D4: D996="S")*(COURIER!C4:C996
))
like this. It was B1000 when i made first. any solution for not to
reduce the number.
Secondly i wanted to change the 996 to the end of excel limit
(65536)any short cut to write this.

thanks alot
with regards
nowfal


--
nowfal
------------------------------------------------------------------------
nowfal's Profile:
http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=388286

Thanks Bob, tested, this is the exact formula i wanted.
by
nowfa

--
nowfa
-----------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...fo&userid=1000
View this thread: http://www.excelforum.com/showthread.php?threadid=38828