Sumproduct + transpose + max

[moccasinus]

Hi, is there a way to modify formula {=SUMPRODUCT(Sheet2!K$2:K$242;TRANSPOSE($B38:$IH38))} for it not to multiply all together, but to return maximum value from array Sheet2!K$2:K$242?
Been trying various ways, multiple criteria index match etc., but none seem to work

 

[Joe4]

I am a bit confused. I am not sure where the SUMPRODUCT and TRANSPOSE come in.
If you just want the max of the range Sheet2!K$2:K$242, why wouldn't you just use the MAX function?

Maybe it would be more helpful if you post a data sample, and show us your expected result.

 

[Fluff]

Further to what Joe has asked, what version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[moccasinus]

Hi, thanks for your replies. I'm usind office 365, platform windows. Hope this clarifies my previous question:
The transpose is to convert from horizontal to vertical data, and sumproduct to multiply all values. I use that for other calculations.
Here, in the pictures I've posted I need to get the maximum value of ProductX in Sheet2!K2:K242 multiplied by the number of corresponding ProductX in TRANSPOSE(B38:IH38).
By using {=SUMPRODUCT(Sheet2!K$2:K$242;TRANSPOSE($B38:$IH38))} I get sum of all the Products, but I only need the one that is maximum

 

[Fluff]

Thanks for that.
However it's still not entirely clear to me what you want, but maybe

=MAX(Sheet2!K$2:K$242*TRANSPOSE($B38:$IH38))

 

[moccasinus]

Notice, that same Products are vertical in one sheet, and horizontal in the other.
In order to get whole sum for it all I use {=SUMPRODUCT(Sheet2!K$2:K$242;TRANSPOSE($B38:$IH38))}
Problems start when I want to find the maximum value in Sheet2!K$2:K$242 and the sumproduct it with TRANSPOSE($B38:$IH38)
I've thought the most logic way would be adding max in front of Sheet2!K$2:K$242, like so: {=SUMPRODUCT(MAX(Sheet2!K$2:K$242);TRANSPOSE($B38:$IH38))}
But then that part of the formula only returns one value, but for sumproduct it has the be the exact number of cells or values as in TRANSPOSE($B38:$IH38)
It is very likely that I need to use something else, not sumproduct, for the formula to multiply the corresponding value by the MAX(Sheet2!K$2:K$242)
Hope I've made myself clearer ?

 

[Fluff]

Did you try the formula I suggested?

 

[moccasinus]

Yup, #value.
I've tried some variations of index match with multiple criteria as well, couldn't get anything either

 

[Fluff]

Do you have any #NUM errors in either set of data?

 

[moccasinus]

No