Forum Discussion
How to count based on partial cell content?
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).
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).
Thanks again!