Dynamic sumproduct range specifier

UHsoccer

Well-known Member
Joined
Apr 3, 2002
Messages
1,023
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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Which column are you looking at to determine the row of the last entry? Are the entries in that column numerical or text based.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828

ADVERTISEMENT

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
Joined
Apr 3, 2002
Messages
1,023
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
Joined
Aug 31, 2005
Messages
5,828

ADVERTISEMENT

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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Aug 31, 2005
Messages
5,828
My suggestion was simply an alternative. It is a single-formula alternative instead of a multi-formula approach.
 

UHsoccer

Well-known Member
Joined
Apr 3, 2002
Messages
1,023
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?
 

Forum statistics

Threads
1,141,727
Messages
5,708,127
Members
421,548
Latest member
douae

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
Top