Sumproduct large with adjustable reference range based on another cell

andrew_milonavic

Board Regular
Joined
Nov 16, 2016
Messages
98
Hi,

Is it possible to change the reference range in a sumproduct large formula to expand based on a value in anther cell?

I'm using this formula: SUMPRODUCT(LARGE((A2:A9=G2)*(B2:B9),1)) and would like the range to expand based on value in another cell, say E1. If E1 was 20, than I would like A9 and B9 to change to A20 and B20.

Possible?

Thanks

Andrew
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
It could be done with names. Define two names

Name: firstColumn RefersTo: =Sheet1!$A$2:INDEX(Sheet1!$A:$A, Sheet1!$E1, 1)
Name: secondColumn RefersTo: =Sheet1!$B$2:INDEX(Sheet1!$B:$B, Sheet1!$E1, 1)

Then your formula would become

=SUMPRODUCT(LARGE(firstColumn=g2)*secondColumn, 1))
 
Last edited:
Upvote 0
Are there any other numbers outside of the range to use?

Also which excel version are you using?
 
Upvote 0
Hi Mikerickson,

Isn't that going to returns errors as sum product doesn't work with blanks, or am I missing something?

Thanks

Andrew
 
Upvote 0
Hey Jasonb75,

I'm not sure I understand your first question. The range is bigger, I just gave an example.

Latest version 1908

Thanks

Andrew
 
Upvote 0
See if this does what you need.

=MAXIFS($B$2:INDEX($B:$B,$E$1),$A$2:INDEX($A:$A,$E$1),$G$2)

My first question was to establish if you were using the number of rows to exclude some of the data, or just to set the size of the formula to ignore blanks below the data.

If it is just to ignore blanks, then

=MAXIFS(B:B,A:A,G2)

Should also work without being too inefficient.
 
Upvote 0
Hey Jasonb75,

That works perfectly and gets around the problem of having blanks!!

I really appreciate your help

Thanks

Andrew
 
Upvote 0
Hi Mikerickson,

Isn't that going to returns errors as sum product doesn't work with blanks, or am I missing something?

Thanks

Andrew

What happened when you tried it?
The OP asked to expand the range depeding on the entry in E1. Accounting for blanks is the responsibility of whoever puts the number in E1.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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