sumproduct to find a maximum value ?

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
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>
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Aladin Akyurek

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

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
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,209
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

Well-known Member
Joined
Apr 11, 2010
Messages
11,005

ADVERTISEMENT

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,209
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

Well-known Member
Joined
Apr 11, 2010
Messages
11,005

ADVERTISEMENT

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,209
[…]
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))),"")
 

Forum statistics

Threads
1,141,062
Messages
5,704,062
Members
421,326
Latest member
pfaustino

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top