sumproduct

  • Thread starter Thread starter nowfal
  • Start date Start date
N

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!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
 
You could use INDIRECT

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