Count entries that contain a word

[OTT]

I have the following formula in my spreadsheet:

=SUMPRODUCT(('Apr-Jun'!$J$2:$J$20000="ACKNOWLEDGEMENT")*('Apr-Jun'!$U$2:$U$20000<>"")*('Apr-Jun'!$X$2:$X$20000=""))

In colummn J I have entries that say ACKNOWLEDGEMENT but I also have entries that say PERMISSION ACKNOLWLEDGEMENT and OLD ACKNOWLEDGEMENT. Can anyone please tell me how I change my formula so that it counts the number of entries that contain the word ACKNOWLEDGEMENT rather than equalling the word it's own?

 

[T. Valko]

I have the following formula in my spreadsheet:

=SUMPRODUCT(('Apr-Jun'!$J$2:$J$20000="ACKNOWLEDGEMENT")*('Apr-Jun'!$U$2:$U$20000<>"")*('Apr-Jun'!$X$2:$X$20000=""))

In colummn J I have entries that say ACKNOWLEDGEMENT but I also have entries that say PERMISSION ACKNOLWLEDGEMENT and OLD ACKNOWLEDGEMENT. Can anyone please tell me how I change my formula so that it counts the number of entries that contain the word ACKNOWLEDGEMENT rather than equalling the word it's own?

One way...

=SUMPRODUCT((ISNUMBER(SEARCH("ACKNOWLEDGEMENT",'Apr-Jun'!$J$2:$J$20000)))*('Apr-Jun'!$U$2:$U$20000<>"")*('Apr-Jun'!$X$2:$X$20000=""))

 

[Jonmo1]

Try

=SUMPRODUCT((ISNUMBER(SEARCH("ACKNOWLEDGEMENT",'Apr-Jun'!$J$2:$J$20000)))*('Apr-Jun'!$U$2:$U$20000<>"")*('Apr-Jun'!$X$2:$X$20000=""))


Change Search to Find if you want it to be case sensitive.


Hope that helps.

 

[OTT]

That works. Thanks for that!

 

[T. Valko]

That works. Thanks for that!

You're welcome. Thanks for the feedback!