Sumproduct range changing with deletions

[Steve]

I have this formula in one of the tabs in my workbook:

=SUMPRODUCT((Sun!$G$3:$G$200="Time missing for
sunday")*(Sun!$J$3:$J$200="Swingshift"))

I understand sumproduct cannot do whole ranges via $G:$G.

If I delete rows 101-200 on the Sun tab, this formula on the main is changed
as below:

=SUMPRODUCT((Sun!$G$3:$G$100="Time missing for
sunday")*(Sun!$J$3:$J$100="Swingshift"))

the 200 s become 100 s.

Is there a way to around that, so that the :200s are not changed to :100s ?

Thanks,

Steve

 

[Lars-Åke Aspelin]

I have this formula in one of the tabs in my workbook:

=SUMPRODUCT((Sun!$G$3:$G$200="Time missing for
sunday")*(Sun!$J$3:$J$200="Swingshift"))

I understand sumproduct cannot do whole ranges via $G:$G.

If I delete rows 101-200 on the Sun tab, this formula on the main is changed
as below:

=SUMPRODUCT((Sun!$G$3:$G$100="Time missing for
sunday")*(Sun!$J$3:$J$100="Swingshift"))

the 200 s become 100 s.

Is there a way to around that, so that the :200s are not changed to :100s ?

Thanks,

Steve

You can use the INDIRECT function for this.

=SUMPRODUCT((INDIRECT("Sun!$G$3:$G$100")="Time missing for
sunday")*(INDIRECT("Sun!$J$3:$J$100")="Swingshift"))

Hope this helps / Lars-Åke

 

[Peo Sjoblom]

Try


=SUMPRODUCT((INDIRECT("Sun!$G$1:$G$200")="Time missing for
sunday")*(INDIRECT("Sun!$J$1:$J$200")="Swingshift"))


the only drawback is that the INDIRECT function is volatile and will
recalculate whenever the worksheet recalculates meaning that if you open the
workbook and just look at it and then close the workbook it will still ask
you if you want to save the workbook and it means if you have lots of
formulas it has the potential to slow down the workbook.

--


Regards,


Peo Sjoblom

 

[John C]

Assuming nothing else is in the columns, you could use the formula like this:
=SUMPRODUCT((Sun!$G$3:$G$65536="Time missing for
sunday")*(Sun!$J$3:$J$65536="Swingshift"))

 

[T. Valko]

Here's another one.

Non-volatile and more efficient than using almost the entire column as a
reference:

=SUMPRODUCT(--(Sun!$G$3:INDEX(Sun!G$3:G$65536,200)="Time"),--(Sun!$J$3:INDEX(Sun!J$3:$J$65536,200)="Swingshift"))

 

[Spiky]

Theoretically, you could also just hit the Delete key (which means the
Clear command) instead of the Delete command. If the formatting can
stay the same.

 

[Steve]

Thanks guys. Already I'm showing some results with some of the solutions. I'm
currently having a bit of a problem with the indirects, but it's probably a
parentheses typo on my part. I'll get it worked out.

Thanks again,

Steve