Excel String-Counting Help!

August 14, 2007 7:22 PM

ExcelFilter: Help me count rows where one string appears in one column and a second string appears in a second!

I have an Excel worksheet with the following setup:

Column A's contains a number of descriptions that include either string1, string2, or string3 -- a typical column A entry might be "(string1) some other text."

Column B's rows contain either StringA, StringB or StringC.

I want to write a formula that will count the number of rows where a cell in column A contains "string1" and the adjoining cell in column B contains "StringA."

The following code (from an excel help site) would work, except String1 never appears by itself in columnA, it's always part of a larger string.

=SUMPRODUCT(--(A16:A20="string1"),--(B16:B20="stringA"))

I've tried adding wildcards, as in:

=SUMPRODUCT(--(A16:A20="*string1*"),--(B16:B20="stringA"))

but no joy.

Help?

posted by Alterscape to computers & internet (5 comments total)

=NOT(ISERROR(SEARCH("target text with possible * wildcards", "text to be searched")))


Me, I'd use a third column to express the truth of ("string1*" adjacent to "stringA"),

=IF(AND(NOT(ISERROR(SEARCH("*cat*", B1))), C1="StringA"), 1, 0)

and sum that truth column.


I don't see how you can pass sumproduct's criterion as a closure, but it may be possible.
posted by orthogonality at 8:27 PM on August 14, 2007

This should work:

=SUMPRODUCT(--(NOT(ISERROR(FIND("string1",A16:A20)))),--(B16:B2="stringA"))

It could be that Excel has a newer function that would simplify this a bit, but I don't have the latest version of Excel. The FIND function will return an error if it can't find the search text, or the index where it found it if it can, so the NOT ISERROR wrapping is necessary.
posted by cerebus19 at 8:28 PM on August 14, 2007

I have to apologize because I don't have Excel installed right now and can't test it out, but I like the COUNTIF function. It has some info on using wild cards too. You would probably have to nest it in an IF to be able to look for two strings though.
posted by cabingirl at 9:27 PM on August 14, 2007

The most elegant method is to use the DCOUNT function. It can be tricky to understand, though.
posted by randomstriker at 11:24 PM on August 14, 2007

I ended up using Orthogonality's solution, but cerebus19 and randomstriker have given very good leads to pursue too, as this class of problem tends to come up fairly frequently in the work I do. Thank you all!
posted by Alterscape at 5:20 AM on August 15, 2007