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

 

[fengflytiger]

=sumproduct((b2:b4<c2:c4)*1,(b2:b4<d2:d4)*1,b2:b4)

 

[Richard Schollar]

Fengflytiger - have you read my post #9 that will hopefully allow you to display your formula?

 

[milesUK]

From the OzGrid forum http://www.ozgrid.com/forum/showthread.php?t=70352

If the values are in B2:D4 then

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

works for me.

 

[barry houdini]

Miles,

That formula sums the lowest value in each row......but the requirement was.....

....look at all of the rows and sum up the prices where Vendor #1 is the least expensive....

That's what Domenic's formula does. It only sums column B when the value in that column is equal to the lowest in the row, hence a result of 45 rather than 115

 

[milesUK]

I do apologise for getting the wrong end of the stick.

I misread the OP and put my own interpretation on it; I saw "what is the minimum total cost if each item was purchased from the 'individually' cheapest supplier". I suspect that I was jumping a step to the final "one cell solution".

 

[malmen]

Amazing formula! Thank you all so much for your help

Best,
Andreas

 

[fengflytiger]

Fengflytiger - have you read my post #9 that will hopefully allow you to display your formula?

yes ,my email is deleted