If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Sumproduct range changing with deletions
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 |
#3
|
|||
|
|||
Sumproduct range changing with deletions
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 |
#4
|
|||
|
|||
Sumproduct range changing with deletions
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 |
#5
|
|||
|
|||
Sumproduct range changing with deletions
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 |
#6
|
|||
|
|||
Sumproduct range changing with deletions
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. |
#7
|
|||
|
|||
Sumproduct range changing with deletions
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 |
Thread Tools | |
Display Modes | |
|
|