SOLVED

How to count based on partial cell content?

Copper Contributor

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 other number shows up in any cell? I'm not looking only for cells that have the number 3 alone, but also 3 combined with other numbers. COUNTIF only seems to work with criteria matching all cell content exactly, not partial content.

 

Or to count the 3s, do I have to separate cell content like 2,3,5,9 into separate columns based on the comma breaks, so there is only one number per cell, then use COUNTIF on the range of columns?

 

I'm using Microsoft Excel for Microsoft 365 MSO, 64-bit, Version 2010 (Build 13328.20478 Click-to-Run).

 

Thanks in advance for any help with this.

 

4 Replies
best response confirmed by Kevin_237 (Copper Contributor)
Solution

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

 

Thank you very much. Your first formula suggestion works fine, using either SUMPRODUCT or SUM. What does the -- in front of ISNUMBER do? I see that the formula doesn't work without it.
Thanks again!
ISNUMBER returns true or false. The "--" coerces those true/false values to their underlying numerical values, 1 and 0 respectively, which are then passed to the sum/sumproduct function.
Got it. Thank you!!
1 best response

Accepted Solutions
best response confirmed by Kevin_237 (Copper Contributor)
Solution

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

 

View solution in original post