# Formula Needed that will return a Supliers Lowest Total Price

1. ## Formula Needed that will return a Supliers Lowest Total Price

I'm trying to find a formula that will return the lowest total supplier price in the attached, but also ignores any total prices that are zero.. I have created a dummy sheet for demonstration purposes, which only contains 3 x supplier prices. In actual fact their are 10 suppliers, some them havent bidded, so "total price" in some instances is zero.

I have entered the following array formula in Cell D10: {=MIN(IF(C7, E7, G7 <>0))}

However, the result this formula is producing is £47,841.28 - which is wrong and is not the lowest total price. The lowest total price is contained in cell G7 £46,694.40.

If anyone can help with this, I would be extremely grateful.  Register To Reply

2. ## Re: Formula Needed that will return a Supliers Lowest Total Price

Hello
Try the following Array formula: ``Please Login or Register  to view this content.``
Hope this helps.
DBY  Register To Reply

3. ## Re: Formula Needed that will return a Supliers Lowest Total Price

Try:

=SMALL(C7:G7,COUNTIF(C7:G7,0)+1)  Register To Reply

4. ## Re: Formula Needed that will return a Supliers Lowest Total Price

Hi Lisa,

Try using below array formula:-

{=MIN(IF((C7:G7>0)*(ISNUMBER(C7:G7)),C7:G7,""))}

see attached:- Find Lowest Total Price.xls

Regards,
DILIPandey

<click on below 'star' if this helps>  Register To Reply