# Dynamic sumproduct range specifier

#### UHsoccer

##### Well-known Member
I use a number of sumproduct formulas that have the start and end cell row hardcoded such as

Code:
``````=(SUMPRODUCT(
--((Closed!\$AP\$4:\$AP\$1500)=\$B6),
--(MONTH(Closed!\$A\$4:\$A\$1500)=D\$1),
Closed!\$H\$4:\$H\$1500))/1000``````

The end cell in this case is 1500

How can I specify to use the end of available data data in column AP, A and H respectively instead of using the 1500? Next month it may need to be 1800, etc

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### NBVC

##### Well-known Member
Which column are you looking at to determine the row of the last entry? Are the entries in that column numerical or text based.

##### MrExcel MVP
I use a number of sumproduct formulas that have the start and end cell row hardcoded such as

Code:
``````=(SUMPRODUCT(
--((Closed!\$AP\$4:\$AP\$1500)=\$B6),
--(MONTH(Closed!\$A\$4:\$A\$1500)=D\$1),
Closed!\$H\$4:\$H\$1500))/1000``````

The end cell in this case is 1500

How can I specify to use the end of available data data in column AP, A and H respectively instead of using the 1500? Next month it may need to be 1800, etc

If you are on Excel on 2003, in Closed convert A3:A1500 (including the header) into a list by means of Data|List|Create List. Do the same with H4:H1500, and AP4:AP1500. This setup allows you to keep the current formula as is for it will adjust itself automatically to changes in the relevant ranges. Proviso: The ranges must be subject to equal growth.

Otherwise:

A1 on the sheet housing the above SumProduct formula:

=MATCH(9.99999999999999E+307,Closed!A:A)

B1:

=IF(ISNUMBER(A1),MAX(ROW(Closed!\$A\$4),A1),A1)

Now change the SumProduct formula to:

Code:
``````=(SUMPRODUCT(
--(Closed!\$AP\$4:INDEX(Closed!AP:AP,\$B\$1)=\$B6),
--(MONTH(Closed!\$A\$4:INDEX(Closed!A:A,\$B\$1))=D\$1),
Closed!\$H\$4:INDEX(Closed!H:H,\$B\$1))/1000``````

#### UHsoccer

##### Well-known Member
Thanks, that's what I was loojing for. I will trry it shortly

#### NBVC

##### Well-known Member

Assuming column AP is the column to look for a "last entry" in, then

If AP is numeric

=(SUMPRODUCT(--((Closed!\$AP\$4:INDEX(Closed!\$AP:\$AP,MATCH(9.999999E+307,Closed!\$AP:\$AP)))=\$B6),--(MONTH(Closed!\$A\$4:INDEX(Closed!\$A:\$A,MATCH(9.999999E+307,Closed!\$AP:\$AP)))=D\$1),Closed!\$H\$4:INDEX(Closed!\$H:\$H,MATCH(9.999999E+307,Closed!\$AP:\$AP))))/1000

if AP is text

=(SUMPRODUCT(--((Closed!\$AP\$4:INDEX(Closed!\$AP:\$AP,MATCH(REPT("Z",255),Closed!\$AP:\$AP)))=\$B6),--(MONTH(Closed!\$A\$4:INDEX(Closed!\$A:\$A,MATCH(REPT("Z",255),Closed!\$AP:\$AP)))=D\$1),Closed!\$H\$4:INDEX(Closed!\$H:\$H,MATCH(REPT("Z",255),Closed!\$AP:\$AP))))/1000

#### UHsoccer

##### Well-known Member
The columns are all equally long and I could use any to get the last row.
Data is date, text and numerics

#### NBVC

##### Well-known Member

If they are equally long, then the above formulas should work fine. You just pick one based on what kind of data is in AP.

##### MrExcel MVP
The columns are all equally long and I could use any to get the last row.
Data is date, text and numerics

Since column A houses dates, the set up I suggested would suffice. Note that it does not require you to repeat the same calculations time and again.

#### NBVC

##### Well-known Member
My suggestion was simply an alternative. It is a single-formula alternative instead of a multi-formula approach.

#### UHsoccer

##### Well-known Member
One question out of curiosity:

Why does the MATCH statement refrerences MATCH(9.999999E+307

From my engineering background, that is one heck of a big number, would 65500 not suffice?

Replies
5
Views
109
Replies
2
Views
452
Replies
3
Views
126
Replies
4
Views
223
Replies
13
Views
412

1,148,172
Messages
5,745,176
Members
423,931
Latest member
thangvan114

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