Sumproduct range changing with deletions

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

On Mon, 4 Aug 2008 10:26:30 -0700, Steve
wrote:

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

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

"Steve" wrote in message
...
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

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"))



--
John C


"Steve" wrote:

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

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" ))

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
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

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.

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

"Steve" wrote:

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