Previous Topic Next Topic

Count only cells containing 2 given words

Closed
BruNL_2017 Posts 3 Registration date Wednesday 15 March 2017 Status Member Last seen 17 March 2017 - 15 Mar 2017 à 09:40
BruNL_2017 Posts 3 Registration date Wednesday 15 March 2017 Status Member Last seen 17 March 2017 - 17 Mar 2017 à 05:14
Dear friends,
I simply get mad trying and trying again to find the right function that may help me to count out of an array only the cells that contain both given keywords (in this case Mark and Luke).

This is my last try:
=COUNTIF(A2:A18,"*Luke*")+COUNTIF(A2:A18,"*Mark*")-COUNTIF(A2:A18,"*Mark*Luke*")-COUNTIF(A2:A18,"*Luke*Mark*")

Thank you in advance for your help.
Related:

4 responses

Réponse 1 / 4
Mazzaropi Posts 1983 Registration date Monday 16 August 2010 Status Contributor Last seen 24 May 2023 147
16 Mar 2017 à 10:05
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
Réponse 2 / 4
Mazzaropi Posts 1983 Registration date Monday 16 August 2010 Status Contributor Last seen 24 May 2023 147
15 Mar 2017 à 14:08
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
Réponse 3 / 4
BruNL_2017 Posts 3 Registration date Wednesday 15 March 2017 Status Member Last seen 17 March 2017
16 Mar 2017 à 04:49
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
Réponse 4 / 4
BruNL_2017 Posts 3 Registration date Wednesday 15 March 2017 Status Member Last seen 17 March 2017
17 Mar 2017 à 05:14
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.