4

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,“=”&(1)) returns the number of blank cells.
  • COUNTIF(B9:J9,“=*1*”) returns a 0.

Any ideas?

Improve this question

1 Answer 1

Reset to default
1

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:

Screenshot showing formula

Setting the cell format to text:

Settings for format text

Improve this answer
2
  • 1
    @bjbk Good edit. Was going to add images but was too lazy :)
    – user7886229
    Commented Jun 12, 2018 at 23:04
  • 1
    It's a great answer. My test was based on a row of phone numbers I had in a spare sheet, which are naturally formatted as text, that's what I get for being hasty. ;)
    – bjbk
    Commented Jun 12, 2018 at 23:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .