using if function..please help
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 |
using if function..please help
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 |
using if function..please help
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 |
All times are GMT +1. The time now is 04:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com