i am trying to use the if function in the following manner.

A B
apple red
apple red
grapes purple
grapes yellow
star n/a

if(B1:B5="n/a",0,countif(A1:A5, "star")

I want to use this formula not only for 'star' but also for 'apple' and
grapes' and many more such fruits which might have n/a later. But, everytime
I use this formula it returns a 1..should it not return a 0 for when the
value in B is n/a?

Please help..
Thank you

Are you trying to say that if any cell in the range B1:B5 contains "n/
a" then you want to return zero? If so, try it this way:

=IF(COUNTIF(B1:B5,"n/a")0,0,COUNTIF(A1:A5,"star"))

Hope this helps.

Pete

On Mar 17, 4:58*pm, sd wrote:
i am trying to use the if function in the following manner. *

A * * * * * * * * *B
apple * * * * * *red
apple * * * * * *red
grapes * * * * *purple
grapes * * * * *yellow
star * * * * * * *n/a

if(B1:B5="n/a",0,countif(A1:A5, "star")

I want to use this formula not only for 'star' but also for 'apple' and
grapes' and many more such fruits which might have n/a later. *But, everytime
I use this formula it returns a 1..should it not return a 0 for when the
value in B is n/a?

Please help..
Thank you

Only a few Excel functions accept ranges. If isn't one of them. You probably
want:
=sumproduct((a1:a5="star")*(b1:b5<"n/a"))

Regards,
Fred

"sd" wrote in message
...
i am trying to use the if function in the following manner.

A B
apple red
apple red
grapes purple
grapes yellow
star n/a

if(B1:B5="n/a",0,countif(A1:A5, "star")

I want to use this formula not only for 'star' but also for 'apple' and
grapes' and many more such fruits which might have n/a later. But,
everytime
I use this formula it returns a 1..should it not return a 0 for when the
value in B is n/a?

Please help..
Thank you