How to SUMPRODUCT with a section of a word

[LaurenHancy]

Hello Me again!!!

I have a bit of a problem with the SUMPRODUCT formular.

I have customer who has many locations in the source data, but i only want to bring back ALL of the locations.

I have used this formular to count all of the calls open:
=SUM(COUNTIF('Source Data'!$J$2:$J$31912,{"*Sony Ericsson*"}))
This works fine.

But how do i put that {"*Sony Ericsson*"} into a SUMPRODUCT?
e.g, I want to find out how many calls have been "Open" in "2010" by all "Sony Ericsson" sites?

This is what i have tried but have failed!
=SUMPRODUCT(--(TEXT('Source Data'!$E$2:$E$40000,"yyyy")=TEXT(2010,"yyyy"))*('Source Data'!$J$2:$J$40000={"*Sony Ericsson*")*('Source Data'!$C$2:$C$40000="Open"))

Hope you can help.

Thanks

KR

Lx

 

[Richard Schollar]

And what is the formula returning (wrong number or error?) and what do you think it should be returning? Examples of what you have in F4, G4 and C5 would help too.