Counting the number of cells which contain a specific character I want to count the number of cells which contain the character “1” in an Apple Numbers table row. When the row of cells include (using a space delimiter between cells),

1234  5671 1 8765 6113 2468


I want it to return a 4, because that is how many cells contain a character “1” in the row.


COUNTIF(B9:J9,“~1”) returns a 0.
COUNTIF(B9:J9,“=1”) returns a 1.
COUNTIF(B9:J9,“*1”) returns a 0.
COUNTIF(B9:J9,“?1”) returns a 0.
COUNTIF(B9:J9,“=”&amp;(1)) returns the number of blank cells.
COUNTIF(B9:J9,“=*1*”) returns a 0.


Any ideas?
Figured it out.

A glitch in the COUNTIF functions prevents it from working with numbers.  This could be easily fixed by the Excel function TEXT() but Numbers doesn't have that function. So...

To solve select B9:J9 and change the Data Format, in the Cell submenu, from Automatic to Text. Then use the function =COUNTIF(B9:J9,”*1*”) to count the amount containing the number. 

Screenshot showing formula when cells formatted as text:



Setting the cell format to text: