Sumproduct with multiple text criteria

[Guest]

I have seen this question answered a couple times but none fit my situation.
I am trying to use the following formula:
=SUMPRODUCT(--(AL2:AL50000="*GGF*"),--(AD2:AD50000="Charleston")) to search
two columns of text and return the count for the rows that meet both
criteria. (If AL2 has the text GGF somewhere in the cells text string and AD2
has the text Charleston in its cell, then it would count 1.)
It is not giving me the count that is correct, it is returning a zero. My
problem is that the text string in column AL contains different strings of
text, and I only want to count the ones that contain the letters "GGF". The
text may appear as part of a longer string, not just by itself in the cell,
and I do want to count it in those cases. It appears the SUMPRODUCT formula
does not let me use the "*GGF*" method of searching a string for specific
text, like the COUNTIF statement does.
Any thoughts on how to get around this problem?
Thanks

 

[Roger Govier]

Hi Robert

Try
=SUMPRODUCT(--(ISNUMBER(FIND("GGF",AL2:AL50000))),--(AD2:AD50000="Charleston"))

Regards

Roger Govier

 

[Guest]

Exactly what I needed - thanks a million!