# 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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.

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

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

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

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

buz,

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

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

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

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
291
Replies
0
Views
402
Replies
16
Views
650
Replies
4
Views
521
Replies
3
Views
84

1,218,796
Messages
6,144,529
Members
450,551
Latest member
Mouse7681

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