SUMPRODUCT partial text

[Frank Rizzo]

Trying to get excel to look in range C19:C63 for the partial word;
ABC 1 (There is one space before the abc and one after ie: ' ABC 1')
and on the same row in the range of L19:L63 for the word ABC ie: 'ABC'.
If this should happen, then count as 1.

My issue is that I can't get excel to find that partial word of ' ABC 1'


=SUMPRODUCT(--(C19:C63="* ABC 1*"),--(L19:L63="ABC"))

Thank you in advance.

Frank

 

[T. Valko]

Trying to get excel to look in range C19:C63 for the partial word;
ABC 1 (There is one space before the abc and one after ie: ' ABC 1')
and on the same row in the range of L19:L63 for the word ABC ie: 'ABC'.
If this should happen, then count as 1.

My issue is that I can't get excel to find that partial word of ' ABC 1'


=SUMPRODUCT(--(C19:C63="* ABC 1*"),--(L19:L63="ABC"))

Thank you in advance.

Frank

SUMPRODUCT doesn't support wildcards. Try it like this:

=SUMPRODUCT(--(ISNUMBER(SEARCH(" ABC 1",C19:C63))),--(L19:L63="ABC"))

 

[Frank Rizzo]

You my friend are a Genius!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Thank you very much!!

 

[T. Valko]

You my friend are a Genius!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thank you very much!!

You're welcome. Thanks for the feedback!