Sumproduct large with adjustable reference range based on another cell

[andrew_milonavic]

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

 

[mikerickson]

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

 

[jasonb75]

Are there any other numbers outside of the range to use?

Also which excel version are you using?

 

[andrew_milonavic]

Hi Mikerickson,

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

Thanks

Andrew

 

[andrew_milonavic]

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

 

[jasonb75]

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.

 

[andrew_milonavic]

Thanks Jasonb75,

I'll try it now and report back.

Thanks

Andrew

 

[andrew_milonavic]

Hey Jasonb75,

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

I really appreciate your help

Thanks

Andrew

 

[mikerickson]

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.