sumproduct to find a maximum value ?

[oldbrewer]

I want the max value for red - suitable for excel 2000,thanks.

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl22, width: 64"]red[/TD]
[TD="class: xl22, width: 64"]15[/TD]
[/TR]
[TR]
[TD="class: xl22"]blue[/TD]
[TD="class: xl22"]17[/TD]
[/TR]
[TR]
[TD="class: xl22"]red[/TD]
[TD="class: xl22"]16[/TD]
[/TR]
[TR]
[TD="class: xl22"]red[/TD]
[TD="class: xl22"]14[/TD]
[/TR]
[TR]
[TD="class: xl22"]blue[/TD]
[TD="class: xl22"]12[/TD]
[/TR]
[TR]
[TD="class: xl22"]blue[/TD]
[TD="class: xl22"]13[/TD]
[/TR]
[TR]
[TD="class: xl22"]red[/TD]
[TD="class: xl22"]15[/TD]
[/TR]
[TR]
[TD="class: xl22"]red[/TD]
[TD="class: xl22"]14[/TD]
[/TR]
[TR]
[TD="class: xl22"]red[/TD]
[TD="class: xl22"]13[/TD]
[/TR]
[TR]
[TD="class: xl22"]red[/TD]
[TD="class: xl22"]12[/TD]
[/TR]
[TR]
[TD="class: xl22"]blue[/TD]
[TD="class: xl22"]19[/TD]
[/TR]
[TR]
[TD="class: xl22"]blue[/TD]
[TD="class: xl22"]18[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

The most appropriate for the version you have:

Control+shift+enter, not just enter:

=MAX(IF(A2:A13="red",B2:B13))

If you must avoid applying control+shift+enter:

=MAX(INDEX((A2:A13="red")*B2:B13,0))

Try not to advertise...

=SUMPRODUCT(MAX((A2:A13="red")*B2:B13))

if I may so.

 

[oldbrewer]

thanks, Aladin - I made the example simple for clarity - the reference to advertising has me flummoxed though......

 

[Aladin Akyurek]

thanks, Aladin - I made the example simple for clarity - the reference to advertising has me flummoxed though......

People pick up what they see... There is for example a site providing the following:

INDEX(...,SMALL(IF(...,ROW(...)),ROWS(1:1))

for returning a sublist. This is literally not robust. And yet it comes back in questions people post here. It's the same story with SUMPRODUCT(MAX(...)), where SUMPRODUCT is taken as control+shift+enter. This equivalence is simply false. And using it we perpetuate the usage.

 

[oldbrewer]

Aladin - your sumproduct formula works perfectly, yet if I change it to MIN it returns 0, why is this, thanks.

 

[Aladin Akyurek]

Aladin - your sumproduct formula works perfectly, yet if I change it to MIN it returns 0, why is this, thanks.

First things first. SUMPRODUCT((MAX((A2:A13="red")*B2:B13)) is not my formula. Not at all.

You just mentioned another reason why not. Such leads to wrong generalizations, that is. just because SMPRODUCT(MAX(...)) works leads to the expection that SUMPRODUCT(MIN((A2:A13="red")*B2:B13)),SUMPRODUCT(AVERAGE((A2:A13="red")*B2:B13)), etc. would also succeed. And of course not.

=SUMPRODUCT(MIN((A2:A13="red")*B2:B13))

will return 0 while min for red is obviously 12 for the data you posted:

>>

SUMPRODUCT(MIN({TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE}*B2:B13))

>>

SUMPRODUCT(MIN({TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE}*{15;17;16;14;12;13;15;14;13;12;19;18}))

>>

SUMPRODUCT(MIN({15;0;16;14;0;0;15;14;13;12;0;0}))

>>

SUMPRODUCT(0)

>> 0

Hope the foregoing stepwise evalution makes clear why we get for a conditional min when SumProduct envelops the calculation.

Another sin, while we are at it, is the insistence on using SUMPRODUCT as a look up function when a a numeric value must be retrieved, sometimes due to the belief that a sum in fact is intended instead of a retrieval.

 

[oldbrewer]

thanks, Aladin, I am sure many will have learnt a lot from this post - I have !

 

[Aladin Akyurek]

Many thanks for raising the issue.

 

[venkatbobbys]

[TABLE="width: 519"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Total Sales[/TD]
[TD] Unit Cost [/TD]
[TD] Total Cost [/TD]
[TD] Profit [/TD]
[TD]CustomerID[/TD]
[TD]Customer[/TD]
[/TR]
[TR]
[TD] $ 984.00[/TD]
[TD] $ 18.00[/TD]
[TD] $ 738.00[/TD]
[TD] $ 246.00[/TD]
[TD]315[/TD]
[TD]The Station[/TD]
[/TR]
[TR]
[TD] $ 1,350.00[/TD]
[TD] $ 14.00[/TD]
[TD] $ 1,260.00[/TD]
[TD] $ 90.00[/TD]
[TD]315[/TD]
[TD]The Station[/TD]
[/TR]
[TR]
[TD] $ 324.00[/TD]
[TD] $ 8.00[/TD]
[TD] $ 216.00[/TD]
[TD] $ 108.00[/TD]
[TD]233[/TD]
[TD]Bert's Bistro[/TD]
[/TR]
[TR]
[TD] $ 1,005.00[/TD]
[TD] $ 14.00[/TD]
[TD] $ 938.00[/TD]
[TD] $ 67.00[/TD]
[TD]14[/TD]
[TD]Smoke House[/TD]
[/TR]
[TR]
[TD] $ 948.00[/TD]
[TD] $ 6.00[/TD]
[TD] $ 474.00[/TD]
[TD] $ 474.00[/TD]
[TD]572[/TD]
[TD]Flagstaff House[/TD]
[/TR]
[TR]
[TD] $ 690.00[/TD]
[TD] $ 14.00[/TD]
[TD] $ 644.00[/TD]
[TD] $ 46.00[/TD]
[TD]23[/TD]
[TD]Two Bitts[/TD]
[/TR]
[TR]
[TD] $ 780.00[/TD]
[TD] $ 14.00[/TD]
[TD] $ 728.00[/TD]
[TD] $ 52.00[/TD]
[TD]6[/TD]
[TD]Pierce Arrow[/TD]
[/TR]
[TR]
[TD] $ 468.00[/TD]
[TD] $ 8.00[/TD]
[TD] $ 312.00[/TD]
[TD] $ 156.00[/TD]
[TD]95[/TD]
[TD]Mamm'a Pasta Palace[/TD]
[/TR]
[TR]
[TD] $ 990.00[/TD]
[TD] $ 14.00[/TD]
[TD] $ 924.00[/TD]
[TD] $ 66.00[/TD]
[TD]846[/TD]
[TD]The Dandelion[/TD]
[/TR]
[TR]
[TD] $ 870.00[/TD]
[TD] $ 14.00[/TD]
[TD] $ 812.00[/TD]
[TD] $ 58.00[/TD]
[TD]557[/TD]
[TD]Carmens[/TD]
[/TR]
[TR]
[TD] $ 1,320.00[/TD]
[TD] $ 28.00[/TD]
[TD] $ 1,120.00[/TD]
[TD] $ 200.00[/TD]
[TD]315[/TD]
[TD]The Station[/TD]
[/TR]
[TR]
[TD] $ 2,343.00[/TD]
[TD] $ 28.00[/TD]
[TD] $ 1,988.00[/TD]
[TD] $ 355.00[/TD]
[TD]846[/TD]
[TD]The Dandelion[/TD]
[/TR]
[TR]
[TD] $ 270.00[/TD]
[TD] $ 14.00[/TD]
[TD] $ 252.00[/TD]
[TD] $ 18.00[/TD]
[TD]557[/TD]
[TD]Carmens[/TD]
[/TR]
[TR]
[TD] $ 420.00[/TD]
[TD] $ 14.00[/TD]
[TD] $ 392.00[/TD]
[TD] $ 28.00[/TD]
[TD]572[/TD]
[TD]Flagstaff House[/TD]
[/TR]
[TR]
[TD] $ 1,749.00[/TD]
[TD] $ 35.00[/TD]
[TD] $ 1,155.00[/TD]
[TD] $ 594.00[/TD]
[TD]14[/TD]
[TD]Smoke House[/TD]
[/TR]
[TR]
[TD] $ 112.00[/TD]
[TD] $ 4.00[/TD]
[TD] $ 56.00[/TD]
[TD] $ 56.00[/TD]
[TD]14[/TD]
[TD]Smoke House[/TD]
[/TR]
[TR]
[TD] $ 576.00[/TD]
[TD] $ 4.00[/TD]
[TD] $ 288.00[/TD]
[TD] $ 288.00[/TD]
[TD]95[/TD]
[TD]Mamm'a Pasta Palace[/TD]
[/TR]
[TR]
[TD] $ 80.00[/TD]
[TD] $ 4.00[/TD]
[TD] $ 40.00[/TD]
[TD] $ 40.00[/TD]
[TD]95[/TD]
[TD]Mamm'a Pasta Palace[/TD]
[/TR]
[TR]
[TD] $ 630.00[/TD]
[TD] $ 14.00[/TD]
[TD] $ 588.00[/TD]
[TD] $ 42.00[/TD]
[TD]14[/TD]
[TD]Smoke House[/TD]
[/TR]
[TR]
[TD] $ 432.00[/TD]
[TD] $ 7.00[/TD]
[TD] $ 336.00[/TD]
[TD] $ 96.00[/TD]
[TD]23[/TD]
[TD]Two Bitts[/TD]
[/TR]
[TR]
[TD] $ 609.00[/TD]
[TD] $ 12.00[/TD]
[TD] $ 348.00[/TD]
[TD] $ 261.00[/TD]
[TD]846[/TD]
[TD]The Dandelion[/TD]
[/TR]
[TR]
[TD] $ 240.00[/TD]
[TD] $ 18.00[/TD]
[TD] $ 180.00[/TD]
[TD] $ 60.00[/TD]
[TD]95[/TD]
[TD]Mamm'a Pasta Palace[/TD]
[/TR]
[TR]
[TD] $ 1,176.00[/TD]
[TD] $ 6.00[/TD]
[TD] $ 588.00[/TD]
[TD] $ 588.00[/TD]
[TD]23[/TD]
[TD]Two Bitts[/TD]
[/TR]
[TR]
[TD] $ 1,080.00[/TD]
[TD] $ 18.00[/TD]
[TD] $ 810.00[/TD]
[TD] $ 270.00[/TD]
[TD]557[/TD]
[TD]Carmens[/TD]
[/TR]
[TR]
[TD] $ 870.00[/TD]
[TD] $ 14.00[/TD]
[TD] $ 812.00[/TD]
[TD] $ 58.00[/TD]
[TD]23[/TD]
[TD]Two Bitts[/TD]
[/TR]
[TR]
[TD] $ 528.00[/TD]
[TD] $ 4.00[/TD]
[TD] $ 264.00[/TD]
[TD] $ 264.00[/TD]
[TD]572[/TD]
[TD]Flagstaff House[/TD]
[/TR]
[TR]
[TD] $ 1,785.00[/TD]
[TD] $ 12.00[/TD]
[TD] $ 1,020.00[/TD]
[TD] $ 765.00[/TD]
[TD]6[/TD]
[TD]Pierce Arrow[/TD]
[/TR]
[TR]
[TD] $ 288.00[/TD]
[TD] $ 18.00[/TD]
[TD] $ 216.00[/TD]
[TD] $ 72.00[/TD]
[TD]846[/TD]
[TD]The Dandelion[/TD]
[/TR]
[TR]
[TD] $ 360.00[/TD]
[TD] $ 7.00[/TD]
[TD] $ 280.00[/TD]
[TD] $ 80.00[/TD]
[TD]6[/TD]
[TD]Pierce Arrow




[/TD]
[/TR]
</tbody>[/TABLE]
How to find customer name with highest sales and the total

 

[Aladin Akyurek]

[…]
How to find customer name with highest sales and the total

For customers with the highest total sales transaction:

Control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($F$2:$F$30,SMALL(IF($A$2:$A$30=MAX($A$2:$A$30),ROW($F$2:$F$30)-ROW($F$2)+1),ROWS($H$2:H2))),"")


For customers with the highest total of total sales transactions:

First, in G2 enter and copy down...

=IF(ISNA(MATCH(F2,$F$1:F1,0)),SUMIFS($A$2:$A$30,$F$2:$F$30,F2),"")

then in I2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($F$2:$F$30,SMALL(IF($G$2:$G$30=MAX($G$2:$G$30),ROW($G$2:$G$30)-ROW($G$2)+1),ROWS($I$2:I2))),"")