Count only cells containing 2 given words
Closed
BruNL_2017
Posts
3
Registration date
Wednesday March 15, 2017
Status
Member
Last seen
March 17, 2017
-
Mar 15, 2017 at 09:40 AM
BruNL_2017 Posts 3 Registration date Wednesday March 15, 2017 Status Member Last seen March 17, 2017 - Mar 17, 2017 at 05:14 AM
BruNL_2017 Posts 3 Registration date Wednesday March 15, 2017 Status Member Last seen March 17, 2017 - Mar 17, 2017 at 05:14 AM
Related:
- Count only cells containing 2 given words
- Tentacle locker 2 - Download - Adult games
- Fnia 2 - Download - Adult games
- Euro truck simulator 2 download free full version pc - Download - Simulation
- Plants vs zombies 2 pc download - Download - Strategy
- Resident evil 2 remake download - Download - Horror
4 responses
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Mar 16, 2017 at 10:05 AM
Mar 16, 2017 at 10:05 AM
BruNL_2017, Good morning.
"...Your function works perfectly only if you substitute FIND with SEARCH:..."
The difference between these functions in Excel is that the FIND is case-sensitive and the SEARCH does not differentiate.
Depending on your data one of them will actually be more suitable for the correct use.
"...Besides, I have found an additional function, much simpler than expected:
=COUNTIF(A2:A18,"*luke*mark*")..."
Are you sure that this formula is providing the correct result?
If in your data will always appear the word luke before the word mark this formula works.
But if any case appears in different order the words the formula will not count.
This is not the case with the formula I suggested earlier.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
"...Your function works perfectly only if you substitute FIND with SEARCH:..."
The difference between these functions in Excel is that the FIND is case-sensitive and the SEARCH does not differentiate.
Depending on your data one of them will actually be more suitable for the correct use.
"...Besides, I have found an additional function, much simpler than expected:
=COUNTIF(A2:A18,"*luke*mark*")..."
Are you sure that this formula is providing the correct result?
If in your data will always appear the word luke before the word mark this formula works.
But if any case appears in different order the words the formula will not count.
This is not the case with the formula I suggested earlier.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Mar 15, 2017 at 02:08 PM
Mar 15, 2017 at 02:08 PM
BruNL_2017, Good afternoon.
Try to use:
=SUMPRODUCT((ISNUMBER(FIND("luke", A2:A18)))*(ISNUMBER(FIND("mark", A2:A18)))*1)
Please, tell us if it worked for you.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Try to use:
=SUMPRODUCT((ISNUMBER(FIND("luke", A2:A18)))*(ISNUMBER(FIND("mark", A2:A18)))*1)
Please, tell us if it worked for you.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
BruNL_2017
Posts
3
Registration date
Wednesday March 15, 2017
Status
Member
Last seen
March 17, 2017
Mar 16, 2017 at 04:49 AM
Mar 16, 2017 at 04:49 AM
Dear Mazzaropi,
thank you very much !
Your function works perfectly only if you substitute FIND with SEARCH:
=SUMPRODUCT((ISNUMBER(SEARCH("luke", $A$2:$A$18)))*(ISNUMBER(SEARCH("mark", $A$2:$A$18)))*1)
Besides, I have found an additional function, much simpler than expected:
=COUNTIF(A2:A18,"*luke*mark*")
Best regards
thank you very much !
Your function works perfectly only if you substitute FIND with SEARCH:
=SUMPRODUCT((ISNUMBER(SEARCH("luke", $A$2:$A$18)))*(ISNUMBER(SEARCH("mark", $A$2:$A$18)))*1)
Besides, I have found an additional function, much simpler than expected:
=COUNTIF(A2:A18,"*luke*mark*")
Best regards
BruNL_2017
Posts
3
Registration date
Wednesday March 15, 2017
Status
Member
Last seen
March 17, 2017
Mar 17, 2017 at 05:14 AM
Mar 17, 2017 at 05:14 AM
Dear Mazzaropi,
indeed you are right ! I did not realize the limits of COUNTIF formula as it will not count the revers order of the 2 text strings.
I agree that SEARCH is more flexible and for me that also means is absolutely reliable should I face words not complying with case-sensitive rule.
Your formula is working perfectly, than. I only wonder how to rise the search to 3 words instead of 3.
Thank you very much again for your amazing support.
indeed you are right ! I did not realize the limits of COUNTIF formula as it will not count the revers order of the 2 text strings.
I agree that SEARCH is more flexible and for me that also means is absolutely reliable should I face words not complying with case-sensitive rule.
Your formula is working perfectly, than. I only wonder how to rise the search to 3 words instead of 3.
Thank you very much again for your amazing support.
