Count Cells but Cells Contain Multiple Data points

[ilovecake]

Hi, I have 5 cells below. I would like to figure a way to count how many cells contain the number 1. When I use "Countif" or "frequency", it does not recognize the second and third cells, because these cells have multiple data (1,2) and (1,7,9).

Any suggestions are appreciated! thanks

1
1,2
1, 7, 9
1
9

[JBeaucaire]

If those values are in column A, this array formula entered with CTRL-SHIFT-ENTER:

=SUM(SEARCH(1,A1:A5))

When you press CTRL-SHIFT-ENTER, you will see braces { } appear around your formula. If you just press ENTER you will break the array and get an error message.

[ilovecake]

thanks - I tried it but it did not work and I copied the formula exactly as-is. Including typing ctrl - shift-enter.

Very much appreciate the help!

[JBeaucaire]

My apologies...it was late, don't know what I was thinking.

Your problem is that the sample you provided is both text and numbers. The only way I could get the proper answer of "4" was to count each separately:

=COUNTIF(A1:A5,"*1*")+COUNTIF(A1:A5,1)

[Teethless mama]

My apologies...it was late, don't know what I was thinking.

Your problem is that the sample you provided is both text and numbers. The only way I could get the proper answer of "4" was to count each separately:

=COUNTIF(A1:A5,"*1*")+COUNTIF(A1:A5,1)

Your formula returns 4, incorrect. Should be 2
1
6, 12
1, 7, 9
7, 11
9

[Teethless mama]

hi, i have 5 cells below. I would like to figure a way to count how many cells contain the number 1. When i use "countif" or "frequency", it does not recognize the second and third cells, because these cells have multiple data (1,2) and (1,7,9).

Any suggestions are appreciated! Thanks

1
1,2
1, 7, 9
1
9

=sum(countif(a1:a8,{1,"* 1","1,*","* 1,*"}))

[NBVC]

How about:

=SUMPRODUCT(--(ISNUMBER(SEARCH(",1,",","&A1:A5&","))))

assuming 1 appears once per cell