sumproduct

[nowfal]

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!D4996="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

 

[Bob Phillips]

You could use INDIRECT

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

 

[nowfal]

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