Sumproduct with a text string

[Iknewthisguy]

Evening,

I'm trying to count the occurrences of a name (i.e. Chris) in column A that have the occurrence of a color (i.e. red) in column B. The problem is in column B I have varying rows of text strings that all contain a color but never in the same place. What I've been trying to do is =sumproduct((A1:A100="Chris")*(B1:B100="red")) but it's not working. I believe it's not working because it's not searching the text string.

Any help would be appreciated.

Thanks.
Chris

 

[FishingIsMoreFun]

Not sure but I think you need a COUNTIF().
If your trying to Find "Chris" in red then I am not sure any Formulas search for formatting.

Can you post a screenshot?

You could most probably acheive this with a VBA function.

 

[Rick Rothstein]

Give this formula a try...

=SUMPRODUCT(ISNUMBER(SEARCH("*chris*",A1:A100))*ISNUMBER(SEARCH("*red*",B1:B100)))

 

[RoryA]

Rick,
No real need for wildcards there, is there? Or am I missing something?

 

[Rick Rothstein]

Rick,
No real need for wildcards there, is there? Or am I missing something?

@Rory - Nope, no need for the wildcards at all... I just got carried away there.

@Chris - Here is the formula Rory is indicating I should have posted...

=SUMPRODUCT(ISNUMBER(SEARCH("chris",A1:A100))*ISNUMBER(SEARCH("red",B1:B100)))

 

[RoryA]

No biggie - just curious. It works either way, and yours is a good reminder that you can use wildcards to pattern match with SEARCH.

 

[FishingIsMoreFun]

How come ISNUMBER() returns a numeric value, but in the formula it will return the text?

Sorry yes I see now it returns the parameters for the SUMPRODUCT() function.

cheers