Thread: Best way to count variable number of text strings?

Attached is a sample file with 3 worksheets. The worksheet, Strings of Interest, has a list of strings that I need to get counts of from the data in the worksheet, Raw Data. I'd like to have a third worksheet like what I'm showing in this file as the sheet, Summary Data. The Summary Data sheet shows the number of occurrences of each String of Interest in the Raw Data sheet.

I know how to create a helper column to count for each unique value, and to then sum all the occurrences, but that feels inefficient. My Raw Data sheet could have anywhere from 500 records, to 40,000 records. My "Strings of Interest" could change somewhat frequently over time. I have lots of files in which I need to do this same thing.

What is a better/more efficient way to do what I'm trying to do?

If you remove the = signs from the column A entries, then in B2 enter:

=COUNTIF('Raw Data'!A:A,"*"&A2&"*")

copied down to easily get count of each item

Using your sample workbook...on the Summary Data sheet

This regular function begins the list of "Strings of Interest"

 
A2: =IF(COUNTA('Strings of Interest'!$A:$A)>=ROWS('Summary Data'!$2:2),
INDEX('Strings of Interest'!$A:$A,ROWS('Summary Data'!$2:2)),"")

and this regular formula returns the count of the Col_A string from the Raw Data sheet

 
B2: =IF(A2<>"",COUNTIF('Raw Data'!$A:$A,"*"&A2),"")

Copy those formulas down as far as you need.

Is that something you can work with?