exception of zero

in the colum "laagste prijs" i want to avoid "0" the formula in this colum is to select the lowest price. the zero means that the shop (here sax) cannot deliver the goods. instead of zero in "laagste prijs" this should be 15,42. how can i solve this?

Try this in cell I2 and copy down: =IF(COUNTIF(F2:G2,0)>0,SUM(F2:G2),SMALL(F2:G2,1))

Is that what you mean?

There are probably much tidier ways of doing this.

I'm sorry this is not working. i do not get get logic of this formula.
if you say there are better ways to do this, please propose those as i'm alwaays interested to do better.
thank you

What do you mean by it's not working? Is it not giving you the right result or giving you an error message.

The logic behind the formula is is checks to see if either values in columns F or G are zero, and if so it sums the two columns on that row, effectively giving you the smallest number other than zero. If neither are zero it does what you had before and uses SMALL to return the smallest of the two numbers.

If you search this forum for SMALL IF you will find another solution to this problem, but it uses an array formula.

Attached is your file with my formula in it. Appears to work to me.

hey, thank you
like often the mistake is in the smallest corner. I used an "," instead of ";". SORRY

my problem is solved!

Glad to help

Originally Posted by BadlySpelledBuoy

Try this in cell I2 and copy down: =IF(COUNTIF(F2:G2,0)>0,SUM(F2:G2),SMALL(F2:G2,1))

Is that what you mean?

There are probably much tidier ways of doing this.

Yes, there are tidier ways...

Non array formula:
=SMALL(F2:G2,COUNTIF(F2:G2,)+1)

Array formula:
=MIN(IF(F2:G2<>0,F2:G2))

Array formula press Ctrl+Shift+Enter, not just Enter