MAX of SUMPRODUCT Fields Match a RANGE of Criteria

Hi guys,

I am trying to find out a formula that can combine the formulas in columns E and F (see attached photo)
I have hundreds of finding value so that cannot use the separate formula.




Thank you so much.

Welcome to the forum.

Doing so would cause a circular reference error, so will not be possible.

Explain the purpose of this and someone may be able to suggest a workaround.

Attach your workbook - instructions at the top of the page.

You did not explain the purpose of this.

Yes, but why? What is the result that you want to see and where? You have not shown a mock-up of what you want.

OK, I have 3 groups (A, B, C as column A in the workbook) and need to find out the max value in these groups.
The value of each group is the result of SUMPRODUCT function based on columns B and C; and the condition as column D (same group name).

ex: Group A value is 74*1 (or cell B4 * C4) + 51*1 (or cell B5 * C5) = 125 => cell E1 formula.
Do the same with group B and C and we got value for each of them (cell E2, E3 formula)
And finally, we use MAX to find out the largest value (Cell F1)=> this is my purpose.

As you can see, I need to use 3 cells (for 3 groups) and 1 cell for the MAX.
So I hope to find a way that I only need one formula that can find out the maximum value of 3 groups (as this example)
[actually, I have so many groups so that find out this way make me save many cells and load]

ARRAY formula in G2

=MAX(MMULT(TRANSPOSE(1*(ROW($B$1:$B$9)>0)),(($B$1:$B$9)*($C$1:$C$9))*(1*($D$1:$D$9=TRANSPOSE($A$1:$A$3)))))

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

Originally Posted by kvsrinivasamurthy

ARRAY formula in G2

=MAX(MMULT(TRANSPOSE(1*(ROW($B$1:$B$9)>0)),(($B$1:$B$9)*($C$1:$C$9))*(1*($D$1:$D$9=TRANSPOSE($A$1:$A$3)))))

Great formula using mmult

Thank you but I have hundreds of value so that I cannot do that.

To whom you are referring to.

Originally Posted by hhhle

Thank you but I have hundreds of value so that I cannot do that.

Mmult will be imposible if max from column
D (unique column D) without helper in Column A

For what it's worth, I believe this is solved and the OP has said (I think) that the MMULT solution works, but that the solution offered in post #9, because of its multi-part nature, is impractical.

REF: My previous Post #8.
All are ARRAY formulas.
I have simplified the formula further

Please Login or Register  to view this content.

To get the group

Please Login or Register  to view this content.