|
countif formula
I am attempting to write a formula that will count cells containing the
word "out" and ignore all others, even if they contain text. The formula I have so far is, =COUNTIF(B6:E42,"*out"). It does not appear to be working. Can anyone offer any advise? Thank you |
countif formula
=COUNTIF(B6:E42,"out")
You don't want a period after it or the star inside the quotes. Now this will only work if only the word out is in the cell. Is the data just going to have out or will there me more words in the cells? (example: 'Over and out' "japc90" wrote: I am attempting to write a formula that will count cells containing the word "out" and ignore all others, even if they contain text. The formula I have so far is, =COUNTIF(B6:E42,"*out"). It does not appear to be working. Can anyone offer any advise? Thank you |
countif formula
That should work, but it won't catch say 'get out of here', for that you
need =COUNTIF(B6:E42,"*out*") and it won't count say 'the ins and outs is not out' twice, for that you need =SUMPRODUCT(LEN(B6:E42)-LEN(SUBSTITUTE(B6:E42,"out","")))/3 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "japc90" wrote in message ups.com... I am attempting to write a formula that will count cells containing the word "out" and ignore all others, even if they contain text. The formula I have so far is, =COUNTIF(B6:E42,"*out"). It does not appear to be working. Can anyone offer any advise? Thank you |
countif formula
Wouldn't this also included unwanted results such as "abOUT" and "OUTer
space"? Bob Phillips wrote: That should work, but it won't catch say 'get out of here', for that you need =COUNTIF(B6:E42,"*out*") and it won't count say 'the ins and outs is not out' twice, for that you need =SUMPRODUCT(LEN(B6:E42)-LEN(SUBSTITUTE(B6:E42,"out","")))/3 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "japc90" wrote in message ups.com... I am attempting to write a formula that will count cells containing the word "out" and ignore all others, even if they contain text. The formula I have so far is, =COUNTIF(B6:E42,"*out"). It does not appear to be working. Can anyone offer any advise? Thank you |
countif formula
It will include them, but it is up to the OP to decide whether that is
unwanted or not. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "AntnyMI" wrote in message ups.com... Wouldn't this also included unwanted results such as "abOUT" and "OUTer space"? Bob Phillips wrote: That should work, but it won't catch say 'get out of here', for that you need =COUNTIF(B6:E42,"*out*") and it won't count say 'the ins and outs is not out' twice, for that you need =SUMPRODUCT(LEN(B6:E42)-LEN(SUBSTITUTE(B6:E42,"out","")))/3 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "japc90" wrote in message ups.com... I am attempting to write a formula that will count cells containing the word "out" and ignore all others, even if they contain text. The formula I have so far is, =COUNTIF(B6:E42,"*out"). It does not appear to be working. Can anyone offer any advise? Thank you |
countif formula
It will definitely need to include other words than just "out." It is
being used for an employee calendar so it will have the employees name in the field as well. Thank you. Bob Phillips wrote: It will include them, but it is up to the OP to decide whether that is unwanted or not. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "AntnyMI" wrote in message ups.com... Wouldn't this also included unwanted results such as "abOUT" and "OUTer space"? Bob Phillips wrote: That should work, but it won't catch say 'get out of here', for that you need =COUNTIF(B6:E42,"*out*") and it won't count say 'the ins and outs is not out' twice, for that you need =SUMPRODUCT(LEN(B6:E42)-LEN(SUBSTITUTE(B6:E42,"out","")))/3 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "japc90" wrote in message ups.com... I am attempting to write a formula that will count cells containing the word "out" and ignore all others, even if they contain text. The formula I have so far is, =COUNTIF(B6:E42,"*out"). It does not appear to be working. Can anyone offer any advise? Thank you |
countif formula
The questions a
- could a cell hold more than one instance of out, and if so should they all be counted? - should about be counted or not? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "japc90" wrote in message oups.com... It will definitely need to include other words than just "out." It is being used for an employee calendar so it will have the employees name in the field as well. Thank you. Bob Phillips wrote: It will include them, but it is up to the OP to decide whether that is unwanted or not. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "AntnyMI" wrote in message ups.com... Wouldn't this also included unwanted results such as "abOUT" and "OUTer space"? Bob Phillips wrote: That should work, but it won't catch say 'get out of here', for that you need =COUNTIF(B6:E42,"*out*") and it won't count say 'the ins and outs is not out' twice, for that you need =SUMPRODUCT(LEN(B6:E42)-LEN(SUBSTITUTE(B6:E42,"out","")))/3 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "japc90" wrote in message ups.com... I am attempting to write a formula that will count cells containing the word "out" and ignore all others, even if they contain text. The formula I have so far is, =COUNTIF(B6:E42,"*out"). It does not appear to be working. Can anyone offer any advise? Thank you |
| All times are GMT +1. The time now is 10:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com