Choose smallest value on each row in an array

malmen

New Member
Hi all,

I'm stuck with a problem where I am trying to sum up the least costly alternatives that belong to a specific category within a large array.

More specifically: I have a large number of rows, where each row represent a material to purchase. Each material can be purchased at a different price from three different vendors, as illustrated below

Material--Price Vendor#1--Price Vendor#2--Price Vendor#3
Paper--100--80--70
Scissors--15--20--30
Ink--30--40--60

What I am trying to do in one cell only is to look at all of the rows and sum up the prices where Vendor #1 is the least expensive. Using the example above, the formula would return 15+30=45

Is this possible?

Andreas

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

milesUK

Active Member
Andreas,

=Sum(MIN(range 1),MIN(range 2),.....)

but if you have many rows that could be very unwieldy. There may be other better ways (probably involving SUMPRODUCT!).

In the meantime if you need to get something working (a multi-cell solution though) place a MIN() formula at the end of each row and then SUM the results.

YvorL

New Member
Hi!There's a poor solution based on your condition (sums only if it's the vendors smallest price).If B1-D1 are the vendor name, and A2-A4 are product names.B5:=IF(B2=SMALL(\$B\$2:\$D\$2;1);B2;0)+IF(B3=SMALL(\$B\$3:\$D\$3;1);B3;0)+IF(B4=SMALL(\$B\$4:\$D\$4;1);B4;0)c5: =IF(C2=SMALL(\$B\$2:\$D\$2;1);C2;0)+IF(C3=SMALL(\$B\$3:\$D\$3;1);C3;0)+IF(C4=SMALL(\$B\$4:\$D\$4;1);C4;0)D5: =IF(D2=SMALL(\$B\$2:\$D\$2;1);D2;0)+IF(D3=SMALL(\$B\$3:\$D\$3;1);D3;0)+IF(D4=SMALL(\$B\$4:\$D\$4;1);D4;0)YvorL

Domenic

MrExcel MVP
Assuming that A2:D4 contains the data, try...

=SUMPRODUCT(--(B2:B4=SUBTOTAL(5,OFFSET(B2:D4,ROW(B2:D4)-ROW(B2),0,1))),B2:B4)

YvorL

New Member

sorry my solutions looks a bit messy.
vendor 1:
B5:=IF(B2=SMALL(\$B\$2:\$D\$2;1);B2;0)+IF(B3=SMALL(\$B\$3:\$D\$3;1);B3;0)+IF(B4=SMALL(\$B\$4:\$D\$4;1);B4;0)
vendor 2:
c5: =IF(C2=SMALL(\$B\$2:\$D\$2;1);C2;0)+IF(C3=SMALL(\$B\$3:\$D\$3;1);C3;0)+IF(C4=SMALL(\$B\$4:\$D\$4;1);C4;0)
vendor 3:
D5: =IF(D2=SMALL(\$B\$2:\$D\$2;1);D2;0)+IF(D3=SMALL(\$B\$3:\$D\$3;1);D3;0)+IF(D4=SMALL(\$B\$4:\$D\$4;1);D4;0)

note: I'm using regional settings, you may have to change ";" to ","
YvorL

Last edited:

Richard Schollar

MrExcel MVP
Assuming that A2:D4 contains the data, try...

=SUMPRODUCT(--(B2:B4=SUBTOTAL(5,OFFSET(B2:D4,ROW(B2:D4)-ROW(B2),0,1))),B2:B4)

That's an amazing formula and looks so much more simple than I expected! milesUK

Active Member

Domenic, could you spare a few minutes to explain that formula please? For me it does NOT work whether enterred as an array formula or not.I assumed SUMPRODUCT would be in there but I could not figure out how to get MIN on each individual row and i'd not used SUBTOTAL enough times to remeber it!

Having reread the OP I assume that Andreas wants 70+15+30=115 and not 15+30=45.

i don't NEED an answer - i'm just trying to learn a bit more. fengflytiger

New Member
<?XML:NAMESPACE PREFIX = C2 /><C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4)< font><C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4)< p><C2:C4)*1,(B2:B4<D2:D4)*1< p><C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4),< p><C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4),and p question <><C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4)<C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4)< C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4),and><C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4)you send href="mailto:fengflytiger@yahoo.com.cn" C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4)you eng<>
</C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4)<C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4)<></C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4),and></C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4),<></C2:C4)*1,(B2:B4<D2:D4)*1<></C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4)<>
<C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4)< p><C2:C4)*1,(B2:B4<D2:D4)*1< p><C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4),< p><C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4),and p question <><C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4)<C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4)< C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4),and><C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4)you send href="mailto:fengflytiger@yahoo.com.cn" C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4)you eng<><C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4),and p question <><C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4)you send p < href="mailto:fengflytiger@yahoo.com.cn"></C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4),and>
</C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4)you>
</C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4)you></C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4)<C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4)<></C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4),and>
</C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4),<>
</C2:C4)*1,(B2:B4<D2:D4)*1<>
</C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4)<>
=SUMPRODUCT((B2:B4<C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4)</C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4)<>
</C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4)you>

Last edited:

fengflytiger

New Member
=SUMPRODUCT((B2:B4<C2:C4)*1,(B2:B4<D2:D4)*1,B2:B4)<?XML:NAMESPACE PREFIX = C2 /><C2:C4)*1<b>
</C2:C4)*1<b>

Last edited:

Richard Schollar

MrExcel MVP
fengflytiger: if you are trying to use a < operator in your formula it would be a good idea to immediately follow it with a space as otherwise it gets interpreted as an opening html tag and hence doesn't display properly.

eg:

=SUMPRODUCT((B2:B4< A1)+0)

Note the space after the < and before the A1.

Replies
6
Views
171
Replies
2
Views
89
Replies
0
Views
381
Replies
8
Views
907
Replies
2
Views
421 Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

1,152,255
Messages
5,769,061
Members
425,515
Latest member
baltusf 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.    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

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