# 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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Which column are you looking at to determine the row of the last entry? Are the entries in that column numerical or text based.

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

Thanks, that's what I was loojing for. I will trry it shortly

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

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

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.

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.

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

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
2
Views
85
Replies
6
Views
494
Replies
4
Views
1K
Replies
3
Views
264
Replies
12
Views
544

1,217,676
Messages
6,137,934
Members
450,099
Latest member
Pushbutton

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