sumproduct to find a maximum value ?

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,993
I want the max value for red - suitable for excel 2000,thanks.

red15
blue17
red16
red14
blue12
blue13
red15
red14
red13
red12
blue19
blue18

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

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,165
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

Board Regular
Joined
Apr 11, 2010
Messages
10,993
thanks, Aladin - I made the example simple for clarity - the reference to advertising has me flummoxed though......
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,165
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

Board Regular
Joined
Apr 11, 2010
Messages
10,993
Aladin - your sumproduct formula works perfectly, yet if I change it to MIN it returns 0, why is this, thanks.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,165
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

Board Regular
Joined
Apr 11, 2010
Messages
10,993
thanks, Aladin, I am sure many will have learnt a lot from this post - I have !
 

venkatbobbys

New Member
Joined
Sep 27, 2018
Messages
1
Total Sales Unit Cost Total Cost Profit CustomerIDCustomer
$ 984.00 $ 18.00 $ 738.00 $ 246.00315The Station
$ 1,350.00 $ 14.00 $ 1,260.00 $ 90.00315The Station
$ 324.00 $ 8.00 $ 216.00 $ 108.00233Bert's Bistro
$ 1,005.00 $ 14.00 $ 938.00 $ 67.0014Smoke House
$ 948.00 $ 6.00 $ 474.00 $ 474.00572Flagstaff House
$ 690.00 $ 14.00 $ 644.00 $ 46.0023Two Bitts
$ 780.00 $ 14.00 $ 728.00 $ 52.006Pierce Arrow
$ 468.00 $ 8.00 $ 312.00 $ 156.0095Mamm'a Pasta Palace
$ 990.00 $ 14.00 $ 924.00 $ 66.00846The Dandelion
$ 870.00 $ 14.00 $ 812.00 $ 58.00557Carmens
$ 1,320.00 $ 28.00 $ 1,120.00 $ 200.00315The Station
$ 2,343.00 $ 28.00 $ 1,988.00 $ 355.00846The Dandelion
$ 270.00 $ 14.00 $ 252.00 $ 18.00557Carmens
$ 420.00 $ 14.00 $ 392.00 $ 28.00572Flagstaff House
$ 1,749.00 $ 35.00 $ 1,155.00 $ 594.0014Smoke House
$ 112.00 $ 4.00 $ 56.00 $ 56.0014Smoke House
$ 576.00 $ 4.00 $ 288.00 $ 288.0095Mamm'a Pasta Palace
$ 80.00 $ 4.00 $ 40.00 $ 40.0095Mamm'a Pasta Palace
$ 630.00 $ 14.00 $ 588.00 $ 42.0014Smoke House
$ 432.00 $ 7.00 $ 336.00 $ 96.0023Two Bitts
$ 609.00 $ 12.00 $ 348.00 $ 261.00846The Dandelion
$ 240.00 $ 18.00 $ 180.00 $ 60.0095Mamm'a Pasta Palace
$ 1,176.00 $ 6.00 $ 588.00 $ 588.0023Two Bitts
$ 1,080.00 $ 18.00 $ 810.00 $ 270.00557Carmens
$ 870.00 $ 14.00 $ 812.00 $ 58.0023Two Bitts
$ 528.00 $ 4.00 $ 264.00 $ 264.00572Flagstaff House
$ 1,785.00 $ 12.00 $ 1,020.00 $ 765.006Pierce Arrow
$ 288.00 $ 18.00 $ 216.00 $ 72.00846The Dandelion
$ 360.00 $ 7.00 $ 280.00 $ 80.006Pierce Arrow





<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
How to find customer name with highest sales and the total
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,165
[…]
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))),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,095,339
Messages
5,443,908
Members
405,256
Latest member
ukboyme

This Week's Hot Topics

Top