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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Which column are you looking at to determine the row of the last entry? Are the entries in that column numerical or text based.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
The columns are all equally long and I could use any to get the last row.
Data is date, text and numerics
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
My suggestion was simply an alternative. It is a single-formula alternative instead of a multi-formula approach.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

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