tweek a formula

buz

Board Regular
Joined
May 30, 2002
Messages
230
This formula:

=SUMPRODUCT((Hrs!$B$2:$B$156=B2)*
(Hrs!$A$2:$A$156>=B32)*
(Hrs!$A$2:$A$156<=C32)*(Hrs!$D$2:$D$156))

is working for me till my DB goes beyond row 156.

Is there a way to 'tweek' this formula so it will keep up with my ever expanding DB?

tfyh

Buz
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Yes - change the 'explicit' cell references to dynamic named range(s). Search here for an eg of how this is done - post back if you need more help.

Paddy
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
For example:

1) Go to Insert | Name | Define
2) Type 'End' as the name, & put this in the refers to:
=MATCH(9.9999999E+307,Hrs!$D:$D)
3) Click add
4) Type Drange as another name, & put this in the refers to:
=OFFSET(Hrs!$A$2,0,0,end-1,4)

This should give you a named range that will expand with the data in cols a:d. Check this by hitting F5 & typing drange in the reference box. Add some more data & do it again - the highlighted area should grow with your data. You can now use the named range to reference your (growing) table:


=SUMPRODUCT((Hrs!$B$2:$B$156=B2)*
(Hrs!$A$2:$A$156>=B32)*
(Hrs!$A$2:$A$156<=C32)*(Hrs!$D$2:$D$156))


becomes:

=sumproduct((index(drange,0,2)=b2)*(index(drange,0,1)=b32)*(index(drange,0,1)-c32)*(index(drange,0,4))


Paddy
 

buz

Board Regular
Joined
May 30, 2002
Messages
230
Paddy - thanks. I was searching the 'dynamic named range' topics and had come up a bit empty handed.

I've been practicing with a copy of my wkbk and will follow your posted example to see what i get.

Off topic - the F5 key you refer to - is that a windows keystroke? I'm using a Mac and don't believe the F5 key relates to XL.

tfyh

Buz
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

re F5 - windows (?) short cut for the go to... option found under edit | go to ..


Paddy
 

buz

Board Regular
Joined
May 30, 2002
Messages
230
Paddy

Did as you said - used copy and paste so as to not make errors in transcription.

when i pasted in the 'new' =sumproduct, XL alerted me to an error it said it corrected so I accepted the change. Now, one error I noticed when i copied it was a '-' where I thought an '=' belonged so i made that change.

Here's the formula as xl corrected it:

=SUMPRODUCT((INDEX(Drange,0,2)=B2)*
(INDEX(Drange,0,1)=B32)*
(INDEX(Drange,0,1)=C32)*(INDEX(Drange,0,4)))

I have yet to compare them, so will afetr I post this.

The result was a zero in my cell - something didn't work

Buz
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

buz,

If you're not making progress you can mail me the sheet & I'll add a few comments etc...check your private messages.

Paddy
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,971
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
A slightly different way of using a dynamic range for your situation.

Per Paddy's instructions

1) Go to Insert | Name | Define
2) Type 'End' as the name, & put this in the refers to:
=MATCH(9.9999999E+307,Hrs!$D:$D)

You define a name for each column that you use such as rA, rB and rD

Refers to would be similar to

=OFFSET(Hrs!$A$2,0,0,End-1,1)
Note:
Use $A$2 for rA, $B$2 for rB and $D$2 for rD

The formula would be similar to

=SUMPRODUCT((rB=B10)*(rA>=C10)*(rA<=D10)*(rD))

HTH Dave
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-09-16 20:53, buz wrote:
Paddy

Did as you said - used copy and paste so as to not make errors in transcription.

when i pasted in the 'new' =sumproduct, XL alerted me to an error it said it corrected so I accepted the change. Now, one error I noticed when i copied it was a '-' where I thought an '=' belonged so i made that change.

Here's the formula as xl corrected it:

=SUMPRODUCT((INDEX(Drange,0,2)=B2)*
(INDEX(Drange,0,1)=B32)*
(INDEX(Drange,0,1)=C32)*(INDEX(Drange,0,4)))

I have yet to compare them, so will afetr I post this.

The result was a zero in my cell - something didn't work

Buz

Since the original formula is

=SUMPRODUCT((Hrs!$B$2:$B$156=B2)*
(Hrs!$A$2:$A$156>=B32)*
(Hrs!$A$2:$A$156<=C32)*(Hrs!$D$2:$D$156))

the new formula must be:

=SUMPRODUCT((INDEX(Drange,0,2)=B2)*(INDEX(Drange,0,1)>=B32)*(INDEX(Drange,0,1)<=C32)*(INDEX(Drange,0,4)))

or

=SUMPRODUCT((INDEX(Drange,0,2)=B2)*(INDEX(Drange,0,1)>=B32)*(INDEX(Drange,0,1)<=C32),(INDEX(Drange,0,4)))
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-09-16 21:38, Dave Patton wrote:

A slightly different way of using a dynamic range for your situation.

Per Paddy's instructions

1) Go to Insert | Name | Define
2) Type 'End' as the name, & put this in the refers to:
=MATCH(9.9999999E+307,Hrs!$D:$D)

You define a name for each column that you use such as rA, rB and rD

Refers to would be similar to

=OFFSET(Hrs!$A$2,0,0,End-1,1)
Note:
Use $A$2 for rA, $B$2 for rB and $D$2 for rD

The formula would be similar to

=SUMPRODUCT((rB=B10)*(rA>=C10)*(rA<=D10)*(rD))

HTH Dave

Four OFFSET formulas instead one would affect the recalc time adversely.

Aladin
 

Forum statistics

Threads
1,144,221
Messages
5,723,115
Members
422,478
Latest member
Rovan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top