=sumproduct(--(A1:A400<""),--isnumber(search("A.A.A",D1:D400)))

=search() doesn't care about upper/lower case
=find() is case sensitive

jmrd wrote:

I am using the following formula to count text that have two conditions
1) Column A is not blank
2) Column D contains a particular word

=sumproduct(--(A1:A400<""),--(D1:D400="*A.A.A.*")). I am getting a value
error. Column D may or may not contain text that has A.A.A. in it somewhere.
Any thoughts?

--

Dave Peterson