Choose smallest value on each row in an array

malmen

New Member
Joined
Sep 23, 2010
Messages
4
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
 

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
Joined
Jan 5, 2003
Messages
388
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
Joined
Sep 3, 2010
Messages
46
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
Joined
Mar 10, 2004
Messages
19,940
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 3, 2010
Messages
46

ADVERTISEMENT

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:

milesUK

Active Member
Joined
Jan 5, 2003
Messages
388

ADVERTISEMENT

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
Joined
Sep 2, 2010
Messages
11
<?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
Joined
Sep 2, 2010
Messages
11
=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
Joined
Apr 19, 2005
Messages
23,707
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.
 
Master Excel Bundle

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

Threads
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.
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