Choose smallest value on each row in an array

[malmen]

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?

Many thanks in advance,
Andreas

 

[milesUK]

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]

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]

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]

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

 

[Richard Schollar]

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]

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]

<?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>

 

[fengflytiger]

=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>

 

[Richard Schollar]

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.