How to count instances of a string occurring in an array of cells

0
votes

I have a spreadsheet that tracks where consumers are referred to. To record individual referrals, users select places from a drop down list. This list then populates a cell, with values separated by commas.

On a separate sheet, I need to count the number of referrals for each referral type. So I need to count the # of times DHHS shows up in the array, for example. I've attempted to do this using the following formula:

=SUM(LEN(range))-LEN(SUBSTITUTE(range,"string",""))/LEN("string")

This is working fine for single word strings, but is not working for multiple word strings like "CHIP Water Inquiry". Any ideas why, and what I can do about it?

1
arraysexcelexcel-formula
Have you tried applying it as an array formula with Ctrl + Shift + Enter? – zipa

1 Answers

1
votes

You need to make two minor corrections, as you are breathtakingly close in your formula. Add a second SUM formula, tally the subtracted length and then house the two subtracted sections within parentheses as you see in the posted formula. As Boris said, Ctrl + Shift + Enter it, as you probably already know. F1 is assumed to hold your "string" that you wish to count.

=(SUM(LEN(range))-SUM(LEN(SUBSTITUTE(range,F1,""))))/LEN(F1)