Sumproduct function

[Jammydan]

Hi

Can anybody help with the below function. My company uses excel 2003 so I can't use the COUNTIFS function

=SUMPRODUCT(--(D11:D490="*"&R5&"*"),--(L11:L490=L6))

The cells in Range D11:D490 contain multiple names and If I use Countif as below it does work, however the wildcard doesn't seem to work in the SUMPRODUCT function??

=COUNTIF(D11:D490,"*"&R5&"*")

Any help much appreciated.

 

[barry houdini]

That's right - you can't use a wildcard in that context, try using this version

=SUMPRODUCT(--ISNUMBER(SEARCH(R5,D11:D490)),--(L11:L490=L6))

 

[Jammydan]

Works great, thanks Barry!