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

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.

Paddy
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
re F5 - windows (?) short cut for the go to... option found under edit | go to ..


Paddy
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)))
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
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
Back
Top