sumproduct formula to count keyword in cells for a given month

=SUMPRODUCT(--(MONTH('Short Term Issues'!$A$2:$A$100)=11),--(YEAR('Short Term Issues'!$A$2:$A$100)=2010),--('Short Term Issues'!$D$2:$D$100="Leaking"))

Im using the above formula to count rows which have the information 'Leaking' contained in column D:D where the date raised in column A:A is within a certain month, in this case 'Nov2010' - Ill be duplicating the formula for subsequent months, and differant key words in other cells

My problem is that this formula only counts cells which are an exact match of the keyword. Can this be modified so that it counts cells where other words are present along with the keyword?? I have tried using a wildcard type function but iv been unsuccessful so far.

Any helps is appreciated, Thanks

Originally Posted by phillipoosis

counts cells where other words are present along with the keyword??

You can embed SEARCH or FIND into your SUMPRODUCT, eg:

=SUMPRODUCT(--(MONTH('Short Term Issues'!$A$2:$A$100)=11),--(YEAR('Short Term Issues'!$A$2:$A$100)=2010),--ISNUMBER(SEARCH("Leaking",'Short Term Issues'!$D$2:$D$100)))

Originally Posted by phillipoosis

Ill be duplicating the formula for subsequent months, and differant key words in other cells

However, given the above I would say you'll be better off looking for alternate methods to SUMPRODUCT - it is not an efficient formula and should be used in moderation.

We could for ex. create a key on 'Short Term Issues' sheet, suppose column F is free:

'Short Term Issues'!F2:
=($A$2-DAY($A2)+1)&"@"&$D2
copied down to F100

We can then dispense with the SUMPRODUCT altogether and revert to the much more efficient COUNTIF approach

=COUNTIF('Short Term Issues'!$F$2:$F$100,DATE(2010,11,1)&"@*Leaking*")

In XL2007 and above we could use COUNTIFS to negate need for helper calcs - in versions prior to that the above technique is useful if you want to maintain a good level of calculation performance.