Using Sumproduct to find a containing word inside a cell

[bouncingbudha]

I'm hoping someone will be able to help

I am using a sumproduct formula and I need it to find the all the cells containing the word Pauline inside the cell.
I have tried the below formula but excel will not accept it as it contains too many arguments

=SUMPRODUCT(ISNUMBER(SEARCH("pauline",January!F3:F501),--(January!$K$3:$K$500='Environmental Totals'!$A$36)))

Is there another way of getting this formula to work

Cheers

[shg]

=SUMPRODUCT(ISNUMBER(SEARCH("pauline",January!F3:F501)), --(January!$K$3:$K$501='Environmental Totals'!$A$36))

Or you could use autofilter.

[Tony Valko]

Need to convert the results of the ISNUMBER array to numbers:

=SUMPRODUCT(--ISNUMBER(SEARCH("pauline",January!F3:F501)), --(January!$K$3:$K$501='Environmental Totals'!$A$36))

Or, using COUNTIFS:

=COUNTIFS(January!F3:F501,"*pauline*",January!$K$3:$K$501,'Environmental Totals'!$A$36)