Combine SUMIF and MIN functions?

[brodprocan]

Hello all,
I'm brand new to this forum (in terms of posting), as I can usually find the answers to my questions by searching other people's posts. But not this time, so I'm hoping somebody can help.

I'm trying to combine the SUMIF and MIN functions, but can't get it to work. As an example of what I want to achieve, see the worksheet below. I have rows of products, and columns of suppliers. Some of the products are supplied by various suppliers, although at different prices. I've used the MIN function in conditional formatting to highlight the cheapest price for each product (i.e. in each row):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Item to be purchased[/TD]
[TD="align: center"]Supplier A[/TD]
[TD="align: center"]Supplier B[/TD]
[TD="align: center"]Supplier C[/TD]
[TD="align: center"]Supplier D[/TD]
[/TR]
[TR]
[TD]Product A[/TD]
[TD="align: center"]$10.00[/TD]
[TD="align: center"]$12.00[/TD]
[TD="align: center"]$15.00[/TD]
[TD="align: center"]$14.00[/TD]
[/TR]
[TR]
[TD]Product B[/TD]
[TD="align: center"]$25.00[/TD]
[TD="align: center"]$22.00[/TD]
[TD="align: center"]$28.00[/TD]
[TD="align: center"]$26.00[/TD]
[/TR]
[TR]
[TD]Product C[/TD]
[TD="align: center"]$35.00[/TD]
[TD="align: center"]$34.00[/TD]
[TD="align: center"]$32.00[/TD]
[TD="align: center"]$40.00[/TD]
[/TR]
[TR]
[TD]Product D[/TD]
[TD="align: center"]$28.00[/TD]
[TD="align: center"]$30.00[/TD]
[TD="align: center"]$31.00[/TD]
[TD="align: center"]$32.00[/TD]
[/TR]
[TR]
[TD]Product E[/TD]
[TD="align: center"]$55.00[/TD]
[TD="align: center"]$54.00[/TD]
[TD="align: center"]$56.00[/TD]
[TD="align: center"]$53.00[/TD]
[/TR]
[TR]
[TD]Total spend per supplier[/TD]
[TD="align: center"]SUMIF (Lowest price)[/TD]
[TD="align: center"]SUMIF (Lowest price)[/TD]
[TD="align: center"]SUMIF (Lowest price)[/TD]
[TD="align: center"]SUMIF (Lowest price)[/TD]
[/TR]
</tbody>[/TABLE]


This is a simplified version, my actual spreadsheet is much bigger, but what I want to do is SUM only the lowest prices under each supplier. I've tried combining the SUMIF and the MIN function in various ways, but I keep getting a zero, or a #VALUE error. Is this because the SUMIF is working down the column while the MIN is working across each row? Is there a better way to do this? With formulas preferably, I'd rather not get into VBA.

Thanks in advance,

 

[billandrew]

You can use the following array formula


Column A
[TABLE="width: 89"]
<tbody>[TR]
[TD]Product A [/TD]
[/TR]
[TR]
[TD]Product B[/TD]
[/TR]
[TR]
[TD]Product C[/TD]
[/TR]
[TR]
[TD]Product D[/TD]
[/TR]
[TR]
[TD]Product E[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

Column B - enter the following =SUM(MIN(IF($A$2:$A$5= "Product A",B$2:B$5))) Following entering the formula hold and select Control Shift & Enter to ensure this becomes an array function. Curly brackets around formula.

"Product A" refer to the cell with the appropriate Product

I hope I explained it well.

 

[billandrew]

This should provide a better explanation. Copy the formula into all other cells and then you will have what you need.

Not too good at explaining things. Hope you understand..

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Supplier A[/TD]
[TD]Supplier B[/TD]
[TD]Supplier C[/TD]
[TD]Supplier D[/TD]
[/TR]
[TR]
[TD]Product A[/TD]
[TD]=SUM(MIN(IF($A$2:$A$5= $A3,B$2:B$5))) [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]SUM Min TOTAL of each supplier[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Item to be purchased[/TD]
[TD="align: center"]Supplier A[/TD]
[TD="align: center"]Supplier B[/TD]
[TD="align: center"]Supplier C[/TD]
[TD="align: center"]Supplier D[/TD]
[/TR]
[TR]
[TD]Product A[/TD]
[TD="align: center"]$10.00[/TD]
[TD="align: center"]$12.00[/TD]
[TD="align: center"]$15.00[/TD]
[TD="align: center"]$14.00[/TD]
[/TR]
[TR]
[TD]Product B[/TD]
[TD="align: center"]$25.00[/TD]
[TD="align: center"]$22.00[/TD]
[TD="align: center"]$28.00[/TD]
[TD="align: center"]$26.00[/TD]
[/TR]
[TR]
[TD]Product C[/TD]
[TD="align: center"]$35.00[/TD]
[TD="align: center"]$34.00[/TD]
[TD="align: center"]$32.00[/TD]
[TD="align: center"]$40.00[/TD]
[/TR]
[TR]
[TD]Product D[/TD]
[TD="align: center"]$28.00[/TD]
[TD="align: center"]$30.00[/TD]
[TD="align: center"]$31.00[/TD]
[TD="align: center"]$32.00[/TD]
[/TR]
[TR]
[TD]Product E[/TD]
[TD="align: center"]$55.00[/TD]
[TD="align: center"]$54.00[/TD]
[TD="align: center"]$56.00[/TD]
[TD="align: center"]$53.00[/TD]
[/TR]
[TR]
[TD]Total spend per supplier[/TD]
[TD="align: center"]38[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]53[/TD]
[/TR]
</tbody>[/TABLE]

Are the numbers in the last row what you want to see to obtain?

 

[pgc01]

Hi
Welcome to the board

Try in E9:

=SUMPRODUCT(--(SUBTOTAL(5,OFFSET($E$4:$H$8,ROW($E$4:$E$8)-MIN(ROW($E$4:$E$8)),,1))=E4:E8),E4:E8)

Copy across




<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >D</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >E</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >F</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >G</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >H</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >I</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Item to be purchased</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Supplier A</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Supplier B</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Supplier C</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Supplier D</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Product A</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">$10.00 </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">$12.00 </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">$15.00 </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">$14.00 </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Product B</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">$25.00 </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">$22.00 </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">$28.00 </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">$26.00 </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>6</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Product C</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">$35.00 </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">$34.00 </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">$32.00 </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">$40.00 </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>7</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Product D</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">$28.00 </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">$30.00 </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">$31.00 </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">$32.00 </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>8</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Product E</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">$55.00 </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">$54.00 </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">$56.00 </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">$53.00 </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>9</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">38</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">22</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">32</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">53</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>10</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=8 style="background:#9CF; padding-left:1em" > [Book1]Sheet1</td></tr></table>

 

[brodprocan]

Thanks billandrew, but I'm not sure how this would work. Why would I include the product name in the formula? And why would I put the formula into the cells where the prices should go?

 

[brodprocan]

Thanks PGC. Your solution works, although I don't understand why. There are five different functions there all nested within each other. When I break them down to try and understand what each one is doing, I get some confusing results. ROW($E$4:$E$8) and MIN(ROW($E$4:$E$8)) each give the same result ("2"), so ROW($E$4:$E$8)-MIN(ROW($E$4:$E$8)) results in "0". Likewise, OFFSET($E$4:$H$8,ROW($E$4:$E$8)-MIN(ROW($E$4:$E$8)),,1) also gives a result of "0", which makes me wonder why it's there. Expanding out from this, the next function (SUBTOTAL(5,OFFSET($E$4:$H$8,ROW($E$4:$E$8)-MIN(ROW($E$4:$E$8)),,1))=E4:E8) gives a #VALUE! error. But then surprisingly, enclosing all of this in SUMPRODUCT(--(SUBTOTAL(5,OFFSET($E$4:$H$8,ROW($E$4:$E$8)-MIN(ROW($E$4:$E$8)),,1))=E4:E8),E4:E8) gives the correct result. Can you explain what's going on here?

 

[billandrew]

You would provide a reference to the Product by creating table similar to the one I provided & Mr. Excel MVP.

 

[pgc01]

Hi

ROW($E$4:$E$8) and MIN(ROW($E$4:$E$8)) each give the same result ("2"), so ROW($E$4:$E$8)-MIN(ROW($E$4:$E$8)) results in "0".

Neither is right.

ROW($E$4:$E$8) results in {4;5;6;7;8}
MIN(ROW($E$4:$E$8)) results in 4

This means that

ROW($E$4:$E$8)-MIN(ROW($E$4:$E$8)) results in {0;1;2;3;4}

Likewise, OFFSET($E$4:$H$8,ROW($E$4:$E$8)-MIN(ROW($E$4:$E$8)),,1) also gives a result of "0", which makes me wonder why it's there.

Like we've seen,
OFFSET($E$4:$H$8,ROW($E$4:$E$8)-MIN(ROW($E$4:$E$8)),,1)

is the same as
OFFSET($E$4:$H$8,{0;1;2;3;4},,1)

this separates the 5 rows of data in the range.

For ex., for the value 1 in the array you get OFFSET($E$4:$H$8,1,,1) which is the second row of data, the values {25,22,28,26}

Expanding out from this, the next function (SUBTOTAL(5,OFFSET($E$4:$H$8,ROW($E$4:$E$8)-MIN(ROW($E$4:$E$8)),,1))=E4:E8) gives a #VALUE! error

The Subtotal(MIN,...), since the Offset() separates the rows, will gives us the min of each row, in this case the values {10;22;32;28;53}


The Sumproduct() adds the values when they are equal to the minimum of the row.

Hope this helps.

 

[brodprocan]

Thanks PGC, I can see now that I made some errors in transposing your formula to the actual spreadsheet I'm using, which has 60 different products across 21 different suppliers. I have to say though I had to re-read your explanation several times, along with a few other websites, before I fully understood your formula.

For the benefit of others who might be as confused as I was (and for my own benefit if I need to refer to this again in the future), the important things to understand here are:

1. how to work with arrays:
https://www.ablebits.com/office-addins-blog/2015/02/25/array-formulas-functions-excel/

and

https://www.ablebits.com/office-addins-blog/2015/03/04/excel-array-formula-examples/


2. the syntax of the OFFSET and SUBTOTAL functions:
https://support.office.microsoft.co...function-C8DE19AE-DD79-4B9B-A14E-B4D906D11B66

and

https://support.office.microsoft.co...function-7B027003-F060-4ADE-9040-E478765B9939


3. the importance of the double unary operator (--) in the SUMPRODUCT function:
McGimpsey & Associates : Excel : Formulae : Why "--"

(and also explained at bottom of first array link above).


Once I got my head around all of this, this is the formula I finished up with for the actual spreadsheet I'm using (not the example supplied in my original post):

=SUMPRODUCT(--(SUBTOTAL(105,OFFSET($C$2:$V$61,ROW($C$2:$C$61)-MIN(ROW($C$2:$C$61)),,1))=C2:C61),C2:C61,$B$2:$B$61)

You'll notice that I've used SUBTOTAL(105, instead of SUBTOTAL(5, . This is because I have some products which are not part of my current inventory, either because they're not available, or there are better products available, or I'm not using them for current projects (but may use them for future projects). I've decided to hide these rows in my spreadsheet, as I don't want them included in the calculations. SUBTOTAL(105, does not count the hidden rows, whereas SUBTOTAL(5, does.

Another modification I made was to add another argument into the SUMPRODUCT function, $B$2:$B$61. This is because I have a column which lists the quantity of each item purchased. This addition means that the price of each product will be multiplied by the quantity before being summed up in the final total, something I didn't consider earlier.

I sometimes find that I need to add new products or new suppliers, so I've included a blank row (61) and column (V) in my range, so that when I do insert a new row or column, the formula will automatically adjust itself to take this into account.

I'm very happy with this solution!

As an addendum, I've also decided to include a row for postage/delivery charges (Row 62) in the overall total for each supplier:

=SUMPRODUCT(--(SUBTOTAL(105,OFFSET($C$2:$V$61,ROW($C$2:$C$61)-MIN(ROW($C$2:$C$61)),,1))=C2:C61),C2:C61,$B$2:$B$61)+C62

This is not included in the main array, because I don't necessarily want the lowest charge. One supplier may have a lower, or in fact no charge for postage/delivery, but have a higher cost per item. I have additional columns in my spreadsheet which calculate the savings made by using one supplier over another, where both offer similar products. From this I can see that one supplier which charges $16.00 for delivery offers a $73.00 saving over another supplier which does not charge for delivery, for exactly the same products. This shows that the delivery charge is well worth the expense, as it's more than covered by the savings made.

I love all the things you can do with Excel!