min function from different columns

hi! I need some help again! I have a sheet setup like this.
a b
1 x 41
2 y 30
3 x 45
4 y 43
5 x 37
6 y 29

I am using the function
=MIN(IF(a1:a6="x",b1:b6)) then C+S+E

this keeps returning zero
How can I correct this formula to actually return the minimum for x instead
of zero

Thanks in advance.
Derek

Hello,

Maybe you have leading/trailing spaces? ...

=MIN(IF(TRIM(a1:a5)="x",b1:b6))

--
Regards,
Zack Barresse, aka firefytr

It
"sonofroy" <[email protected]> wrote in message
news:[email protected]...
> hi! I need some help again! I have a sheet setup like this.
> a b
> 1 x 41
> 2 y 30
> 3 x 45
> 4 y 43
> 5 x 37
> 6 y 29
>
> I am using the function
> =MIN(IF(a1:a6="x",b1:b6)) then C+S+E
>
> this keeps returning zero
> How can I correct this formula to actually return the minimum for x
> instead
> of zero
>
> Thanks in advance.
> Derek
>

Did you enter the formula with ctrl + shift & enter? Do you have any blank
cells in the B range? What do you mean with "Then C+S+E" If you mean that you
would have one formula with non-contiginous ranges with criteria it won't
work. If you have blanks you can change to

=MIN(IF((A1:A6="x")*(B1:B6<>""),B1:B6))



Regards,

Peo Sjoblom

"sonofroy" wrote:

> hi! I need some help again! I have a sheet setup like this.
> a b
> 1 x 41
> 2 y 30
> 3 x 45
> 4 y 43
> 5 x 37
> 6 y 29
>
> I am using the function
> =MIN(IF(a1:a6="x",b1:b6)) then C+S+E
>
> this keeps returning zero
> How can I correct this formula to actually return the minimum for x instead
> of zero
>
> Thanks in advance.
> Derek
>

See if this works for you:
=MIN(IF(($A$1:$A$6="x"),$B$1:$B$6))
--
Regards,
Ron

Shouldn't make a difference with the parenthasis. It's already contained
within the correct syntax..

--
Regards,
Zack Barresse, aka firefytr

"Ron Coderre" <[email protected]> wrote in message
news:[email protected]...
> See if this works for you:
> =MIN(IF(($A$1:$A$6="x"),$B$1:$B$6))
> --
> Regards,
> Ron
>

That is . . .
=MIN(IF(A1:A6="x",B1:B6))works for me with and without CSE

--
Regards,
Ron

C+S+E = Ctrl+shift+enter. Yes there are zeros in the b range your formula
work i did not jnow the )*(

Thanks a bunch!!!!

"Peo Sjoblom" wrote:

> Did you enter the formula with ctrl + shift & enter? Do you have any blank
> cells in the B range? What do you mean with "Then C+S+E" If you mean that you
> would have one formula with non-contiginous ranges with criteria it won't
> work. If you have blanks you can change to
>
> =MIN(IF((A1:A6="x")*(B1:B6<>""),B1:B6))
>
>
>
> Regards,
>
> Peo Sjoblom
>
> "sonofroy" wrote:
>
> > hi! I need some help again! I have a sheet setup like this.
> > a b
> > 1 x 41
> > 2 y 30
> > 3 x 45
> > 4 y 43
> > 5 x 37
> > 6 y 29
> >
> > I am using the function
> > =MIN(IF(a1:a6="x",b1:b6)) then C+S+E
> >
> > this keeps returning zero
> > How can I correct this formula to actually return the minimum for x instead
> > of zero
> >
> > Thanks in advance.
> > Derek
> >

Which means you are only looking at one cell - the upper most cell in the
range, in this case, A1. Use the Formula Auditor to step through your
formula and see how it's not actually performing without being entered with
Ctrl + Shift + Enter.

--
Regards,
Zack Barresse, aka firefytr

"Ron Coderre" <[email protected]> wrote in message
news:[email protected]...
> That is . . .
> =MIN(IF(A1:A6="x",B1:B6))works for me with and without CSE
>
> --
> Regards,
> Ron

Try putting a value of not x in A1:A6 with a value in B smaller than all the
values associated with x. Still works?

--
HTH

RP

"Ron Coderre" <[email protected]> wrote in message
news:[email protected]...
> That is . . .
> =MIN(IF(A1:A6="x",B1:B6))works for me with and without CSE
>
> --
> Regards,
> Ron