Ask Your Question

Count cells containing certain text

asked 2019-03-22 21:57:07 +0200

Henrik Pedersen gravatar image

updated 2019-03-24 22:22:46 +0200

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

=COUNTIF(FIND(D3:W114,C1)) 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.C:\fakepath\WO.ots

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-03-22 22:44:59 +0200

m.a.riosv gravatar image

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.

edit flag offensive delete link more


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).

Mike Kaganski gravatar imageMike Kaganski ( 2019-03-23 09:29:40 +0200 )edit

Or can be done with regular expressions.

m.a.riosv gravatar imagem.a.riosv ( 2019-03-23 10:04:38 +0200 )edit

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

m.a.riosv gravatar imagem.a.riosv ( 2019-03-23 10:12:02 +0200 )edit

answered 2019-03-23 10:31:40 +0200

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

edit flag offensive delete link more


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 ;-)

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.

Henrik Pedersen gravatar imageHenrik Pedersen ( 2019-03-24 22:03:22 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-03-22 21:57:07 +0200

Seen: 1,079 times

Last updated: Mar 24 '19