sumproduct to find a maximum value ?

[oldbrewer]

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

red	15
blue	17
red	16
red	14
blue	12
blue	13
red	15
red	14
red	13
red	12
blue	19
blue	18

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[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]

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

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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