# tweek a formula

#### buz

##### Board Regular
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

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

##### MrExcel MVP
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.

##### MrExcel MVP
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)
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))

#### buz

##### Board Regular
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

##### MrExcel MVP

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

#### buz

##### Board Regular

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

##### MrExcel MVP

buz,

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

#### Dave Patton

##### Well-known Member
A slightly different way of using a dynamic range for your situation.

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

##### MrExcel MVP
On 2002-09-16 20:53, buz wrote:

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

##### MrExcel MVP
On 2002-09-16 21:38, Dave Patton wrote:

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

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

Replies
1
Views
423
Replies
3
Views
259
Replies
4
Views
233
Replies
1
Views
116
Replies
3
Views
144

### Forum statistics

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.

### Which adblocker are you using?    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

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