Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Countif statement
Hello from Steved
How do I have the below work, I want Yes to be displayed if Column does not have OK in the M Column please. =IF(COUNTIF($M$1:$M$4999,"<OK"), "YES","NO") The above formula resides in Cell L1 Thankyou. |
#2
|
|||
|
|||
try
=if(countif($M$1:$M$4999,"OK")0,"NO","YES") "Steved" wrote: Hello from Steved How do I have the below work, I want Yes to be displayed if Column does not have OK in the M Column please. =IF(COUNTIF($M$1:$M$4999,"<OK"), "YES","NO") The above formula resides in Cell L1 Thankyou. |
#3
|
|||
|
|||
Hello bj from Steved
If a figure sat 12,975 appears instead off OK,I would like it to say No Please excuse me for the misunderstanding. As at the moment if their is a change with your formula nothing happens. Thankyou. "bj" wrote: try =if(countif($M$1:$M$4999,"OK")0,"NO","YES") "Steved" wrote: Hello from Steved How do I have the below work, I want Yes to be displayed if Column does not have OK in the M Column please. =IF(COUNTIF($M$1:$M$4999,"<OK"), "YES","NO") The above formula resides in Cell L1 Thankyou. |
#4
|
|||
|
|||
Steved,
If you mean any figure not just 12,975 then try: =IF(OR(COUNTIF($M$1:$M$4999,"OK"),COUNT($M$1:$M$49 99)), "NO","YES") -- HTH Sandy Replace@mailinator with @tiscali.co.uk "Steved" wrote in message ... Hello bj from Steved If a figure sat 12,975 appears instead off OK,I would like it to say No Please excuse me for the misunderstanding. As at the moment if their is a change with your formula nothing happens. Thankyou. "bj" wrote: try =if(countif($M$1:$M$4999,"OK")0,"NO","YES") "Steved" wrote: Hello from Steved How do I have the below work, I want Yes to be displayed if Column does not have OK in the M Column please. =IF(COUNTIF($M$1:$M$4999,"<OK"), "YES","NO") The above formula resides in Cell L1 Thankyou. |
#5
|
|||
|
|||
I may not understand what you want.
If I read the original message correctly, no Cell in M1 to M4999 can have an "OK" for L1 to say "Yes". if Any Cell has an "OK" then L1 will say "No" Do you want L1 to refer only to M1, L2 only to M2? Do you also want it to say "NO" if any number shows in any of the cells "Steved" wrote: Hello bj from Steved If a figure sat 12,975 appears instead off OK,I would like it to say No Please excuse me for the misunderstanding. As at the moment if their is a change with your formula nothing happens. Thankyou. "bj" wrote: try =if(countif($M$1:$M$4999,"OK")0,"NO","YES") "Steved" wrote: Hello from Steved How do I have the below work, I want Yes to be displayed if Column does not have OK in the M Column please. =IF(COUNTIF($M$1:$M$4999,"<OK"), "YES","NO") The above formula resides in Cell L1 Thankyou. |
#6
|
|||
|
|||
Hello from Steved
$M$1:$M$4999 if M1:M4999 all has "OK" the answer is yes if M1:M4999 all has not got "OK" the answer is No I'm sorry about the confusion the cell L1 is where I will put your formula. Cheers. "bj" wrote: I may not understand what you want. If I read the original message correctly, no Cell in M1 to M4999 can have an "OK" for L1 to say "Yes". if Any Cell has an "OK" then L1 will say "No" Do you want L1 to refer only to M1, L2 only to M2? Do you also want it to say "NO" if any number shows in any of the cells "Steved" wrote: Hello bj from Steved If a figure sat 12,975 appears instead off OK,I would like it to say No Please excuse me for the misunderstanding. As at the moment if their is a change with your formula nothing happens. Thankyou. "bj" wrote: try =if(countif($M$1:$M$4999,"OK")0,"NO","YES") "Steved" wrote: Hello from Steved How do I have the below work, I want Yes to be displayed if Column does not have OK in the M Column please. =IF(COUNTIF($M$1:$M$4999,"<OK"), "YES","NO") The above formula resides in Cell L1 Thankyou. |
#7
|
|||
|
|||
=(COUNTIF($M$1:$M$4999,"OK")=ROWS($M$1:$M$4999))+0
Custom format the formula cell as: [=0]"No";[=1]"Yes" Steved wrote: Hello from Steved $M$1:$M$4999 if M1:M4999 all has "OK" the answer is yes if M1:M4999 all has not got "OK" the answer is No [...] |
#8
|
|||
|
|||
try
=if(or(M1:M4999<"OK"),"NO","YES") entered as an array formula (Control-Shift-Enter) "Steved" wrote: Hello from Steved $M$1:$M$4999 if M1:M4999 all has "OK" the answer is yes if M1:M4999 all has not got "OK" the answer is No I'm sorry about the confusion the cell L1 is where I will put your formula. Cheers. "bj" wrote: I may not understand what you want. If I read the original message correctly, no Cell in M1 to M4999 can have an "OK" for L1 to say "Yes". if Any Cell has an "OK" then L1 will say "No" Do you want L1 to refer only to M1, L2 only to M2? Do you also want it to say "NO" if any number shows in any of the cells "Steved" wrote: Hello bj from Steved If a figure sat 12,975 appears instead off OK,I would like it to say No Please excuse me for the misunderstanding. As at the moment if their is a change with your formula nothing happens. Thankyou. "bj" wrote: try =if(countif($M$1:$M$4999,"OK")0,"NO","YES") "Steved" wrote: Hello from Steved How do I have the below work, I want Yes to be displayed if Column does not have OK in the M Column please. =IF(COUNTIF($M$1:$M$4999,"<OK"), "YES","NO") The above formula resides in Cell L1 Thankyou. |
#9
|
|||
|
|||
"Aladin Akyurek" wrote in message
... =(COUNTIF($M$1:$M$4999,"OK")=ROWS($M$1:$M$4999))+0 Aladin, May I ask, when you have hard coded $M$4999 into the formula and ROWS($M$1:$M$4999) returns 4999, why did you not just use =4999? I thought that it was to account for inserted rows above the data but if you insert 10 rows the fomula automatically changes to: =(COUNTIF($M$11:$M$5009,"OK")=ROWS($M$11:$M$5009)) +0 and the ROWS($M$11:$M$5009) of course still evaluates to 4999 -- Puzzled but wanting to learn, Sandy Replace@mailinator with @tiscali.co.uk |
#10
|
|||
|
|||
Sandy Mann wrote: "Aladin Akyurek" wrote in message ... =(COUNTIF($M$1:$M$4999,"OK")=ROWS($M$1:$M$4999)) +0 Aladin, May I ask, when you have hard coded $M$4999 into the formula and ROWS($M$1:$M$4999) returns 4999, why did you not just use =4999? I thought that it was to account for inserted rows above the data but if you insert 10 rows the fomula automatically changes to: =(COUNTIF($M$11:$M$5009,"OK")=ROWS($M$11:$M$5009)) +0 and the ROWS($M$11:$M$5009) of course still evaluates to 4999 If rows in between would be deleted for some reason, the formula would break down, hence ROWS(...). |
#11
|
|||
|
|||
If rows in between would be deleted for some reason, the formula would
break down, hence ROWS(...). Ah! I thought of rows before, rows after......... never thought of rows between -- Thank you Aladin, Sandy Replace@mailinator with @tiscali.co.uk |
#12
|
|||
|
|||
Thanks bj
"bj" wrote: try =if(or(M1:M4999<"OK"),"NO","YES") entered as an array formula (Control-Shift-Enter) "Steved" wrote: Hello from Steved $M$1:$M$4999 if M1:M4999 all has "OK" the answer is yes if M1:M4999 all has not got "OK" the answer is No I'm sorry about the confusion the cell L1 is where I will put your formula. Cheers. "bj" wrote: I may not understand what you want. If I read the original message correctly, no Cell in M1 to M4999 can have an "OK" for L1 to say "Yes". if Any Cell has an "OK" then L1 will say "No" Do you want L1 to refer only to M1, L2 only to M2? Do you also want it to say "NO" if any number shows in any of the cells "Steved" wrote: Hello bj from Steved If a figure sat 12,975 appears instead off OK,I would like it to say No Please excuse me for the misunderstanding. As at the moment if their is a change with your formula nothing happens. Thankyou. "bj" wrote: try =if(countif($M$1:$M$4999,"OK")0,"NO","YES") "Steved" wrote: Hello from Steved How do I have the below work, I want Yes to be displayed if Column does not have OK in the M Column please. =IF(COUNTIF($M$1:$M$4999,"<OK"), "YES","NO") The above formula resides in Cell L1 Thankyou. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding an AND Statement to COUNTIF | Excel Worksheet Functions | |||
Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions | |||
COUNTIF Statement with Multiple Conditions in Different Ranges | Excel Worksheet Functions | |||
Countif Function with and Statement | Excel Worksheet Functions | |||
countif statement | Excel Worksheet Functions |