Sumproduct to Count Text

Hello everyone, and thanks in advanced for your help. Here is my problem:

I have Excel 2003 at my office and I am trying to use the sumproduct function to count the number of times certain text appears. I receive data weekly which contain the names of firms that we do business with. Sounds easy enough, but the problem is that sometimes the firms are listed differently (eg. ABC Inc; ABC, Inc.; ABC). My solution was to add another sumproduct term, but I am unsure if the data will show an alternate firm name in the coming weeks.

Is there a way to use sumproduct to count all the cells of text that contain the phrase "ABC" indeterminate of what else is in the cell?

Current formula: =SUMPRODUCT(--(Data!A2:A3000="person")*(--(Data!G2:G3000="ABC")+(--(Data!G2:G3000="ABC, LLC"))+(--(Data!G2:G3000="ABC LLC"))))

Preferred formula: =SUMPRODUCT(--(Data!A2:A3000="person")*(--(Data!G2:G3000=("cell contains ABC")))

Thank you again for your expertise.

Hi there, welcome to the Forum!

Try using ISNUMBER(FIND()) like this

=SUMPRODUCT(--(Data!A2:A3000="person")*--(ISNUMBER(FIND("ABC",Data!G2:G3000))))

Glad i could help, could you please mark this thread as solved?

Originally Posted by DGagnon

=SUMPRODUCT(--(Data!A2:A3000="person")*--(ISNUMBER(FIND("ABC",Data!G2:G3000))))

This formula will work but, really, you are mixing up syntaxes unnecessarily here, if you use * you don't need -- and vice versa, so probably you would normally use either this version

=SUMPRODUCT((Data!A2:A3000="person")*ISNUMBER(FIND("ABC",Data!G2:G3000)))

....where multiplying the two arrays using * implicitly co-erces the TRUEs to 1s and the FALSEs to zeroes, or this one.....

=SUMPRODUCT(--(Data!A2:A3000="person"),--ISNUMBER(FIND("ABC",Data!G2:G3000)))

where -- explicitly co-erces each array