How to count based on partial cell content?

If multiple numbers are combined in a single cell, such as "2,3,5,9", is there a function or nesting of functions that will allow me to search a range of cells and count the number of times "3" or ot...

Formulas and Functions

JMB17
Apr 16, 2021
Kevin_237

You could split it to separate cells. Depending on what else you might be using the data for, it might be more functional. But, I believe you could also count them in place. Assuming there's no spaces in your data, and if you're looking for the number of cells containing "3", then you could try:

=SUMPRODUCT(--ISNUMBER(SEARCH(",3,",A1:A10)))

If looking for the total number of occurrences and it could appear more than once in the same cell, then you could try:

=SUMPRODUCT(LEN(","&A1:A10&",")-LEN(SUBSTITUTE(","&A1:A10&",",",3,","")))/3

With office 365 and dynamic arrays, think you can just use the sum function instead of sumproduct. If your data has spaces, you could use Substitute(A1:A10," ","") wherever the range reference occurs in the formula(s).

Forum Discussion

How to count based on partial cell content?

Resources