Small with If condition to return smallest value greater than 0

[asad]

Hi All,

I have the below formula. It works fine if I have 25 values and it gives me the smallest value. But the problem is that more often than not, we have only 2, 3 or anywhere upto 10 values. So the result will always be o with the formula. Is there a way to resolve this?

=SMALL(IF(('Sessions list'!$N$1:$N$25=$B14)*(LEFT('Sessions list'!$B$1:$B$25,10)=TEXT(E$6,"dd.mm.yyyy")),'Sessions list'!$J$1:$J$25,0),1)

enetered with ctrl+shift+enter

Thanks

 

[kvsrinivasamurthy]

Try.

=SMALL(IF(('Sessions list'!$N$1:$N$25=$B14)*(LEFT('Sessions list'!$B$1:$B$25,10)=TEXT(E$6,"dd.mm.yyyy")),'Sessions list'!$J$1:$J$25,""),1)

If you have 0 values in 'Sessions list'!$J$1:$J$25 and you don't want 0 as smallest value then

=SMALL(IF(('Sessions list'!$N$1:$N$25=$B14)*(LEFT('Sessions list'!$B$1:$B$25,10)=TEXT(E$6,"dd.mm.yyyy"))*('Sessions list'!$J$1:$J$25<>0),'Sessions list'!$J$1:$J$25,""),1)

 

[asad]

Try.

=SMALL(IF(('Sessions list'!$N$1:$N$25=$B14)*(LEFT('Sessions list'!$B$1:$B$25,10)=TEXT(E$6,"dd.mm.yyyy")),'Sessions list'!$J$1:$J$25,""),1)

If you have 0 values in 'Sessions list'!$J$1:$J$25 and you don't want 0 as smallest value then

=SMALL(IF(('Sessions list'!$N$1:$N$25=$B14)*(LEFT('Sessions list'!$B$1:$B$25,10)=TEXT(E$6,"dd.mm.yyyy"))*('Sessions list'!$J$1:$J$25<>0),'Sessions list'!$J$1:$J$25,""),1)

Thanks for looking into my formula issue.
But there are no 0s in the range. The way it calculates, if there are no matching arguments in the 2 conditions in my formula, the formula just calculates those cells to be 0 and then gives me 0 as the answer.

 

[asad]

Below is the screen shot of my data

 

[kvsrinivasamurthy]

Have you tried both the formulas. If still problem is there Pl upload file.

 

[asad]

Thanks a lot kvsrinivasamurthy, first one actually works.
Sorry, I misunderstood your solutions but then looked again.
Thanks a lot.