Count cells containing certain text

How can I count the cells in a range who’s contents are contained in a larger string?

returns #VALUE!, or ERR:511 as an array formula
the cells are blank or have a letter.

Application: Count railroad cars by destinations in a yard. A single character indicates the destination, and I want to know how many cars I have for a train I must build which brings cars to multiple destinations. Next I want to highlight those cars (cells), to help sort.WO.ots

Please take a look to COUNTIFS help, it is like COUNTIF but allowing several criteria at once, and if only one it’s easy add more in the future. There are examples on how to use it.

I’m not sure, but it seems not quite suitable here: iiuc, OP needs cells which have any of criteria matching (OR), while COUNTIFS only counts rows where all criteria are true simultaneously (AND).

Or can be done with regular expressions.

Please attach a minimal sample file, to see what you like to get.

If, say, your “train destinations” in cell C1 look like “abd”, and in your “cars destinations” in D3:W114 are single characters like “a”, “b”, “c”, “d”, “e”, …, then you can count only those cars that go to “a” or “b” or “d” using


and you may apply highlighting to those selected cars using conditional formatting with formula


SUMPRODUCT+ISNUMBER+FIND works well - thanks!

I’m bothered that before I opened my template to fix it, I closed without saving the working file where I had first found a simpler function, got side-tracked, and forgot that simple function, but it’s all better now. I like longer functions anyway :wink:

As to the conditional formatting, I haven’t got your suggestion working but will retry eventually. Meanwhile, I set a conditional background when cell value is the first character in $C$1 if there is a character, a different conditional background if there is a second character in $C$1 and it matches, a third, and a fourth. I sorta want the different colors now that I have them.