1

Most cells in the column have a single integer in them, e.g. 5. Some cells have a range as a string, e.g. '4-5. (Let's ignore the problem where someone types 4-5 and the app interprets it as April 5th, and stores a date value.) Some cells have a single integer as text, e.g. '5. How can I count the number of cells in the column that have a specific integer anywhere in them, as text or number value?

  • =COUNTIF(A2:A88, 5) — counts 5 and '5, does not count '4-5
  • =COUNTIF(A2:A88, "5") — counts 5 and '5, does not count '4-5
  • =COUNTIF(A2:A88, "*5*") — counts '5 and '4-5, does not count 5

Ideally I want to coerce the displayed value to be text that is searched. (That would also solve the April 5th problem.) I want a solution that includes all of 5, '5, '4-5, and '5-6.

1
  • My workaround is to add a separate column with =TO_TEXT() on the original column, and then use the *5* solution on that new column.
    – Phrogz
    Feb 16 at 3:04

1 Answer 1

2

Formula Option 1

=SUMPRODUCT(REGEXMATCH("'" & A2:A88, "5"))

# Equivalent to

=ARRAYFORMULA(SUM(REGEXMATCH("'" & A2:A88, "5")))

Formula Option 2

=SUMPRODUCT(IFERROR(FIND("5",A2:A88)>0))

Exaplantion

  1. SUMPRODUCT is an array function so it returns the results for the entire range and is a math function so it coerces an array of TRUE & FALSE values to an array of 1 & 0 values.
  2. REGEXMATCH version requires prepending a text character to coerce all values to text. ' was the character I chose.
  3. FIND version uses >0 to generate the TRUE values and uses IFERROR to handle #VALUE! errors when there is no match (FALSE).

Formula Option 3

Adapting your COUNTIF approach but without a helper column

=SUMPRODUCT(COUNTIF("'" & A2:A88, "*5*")

# or

=ARRAYFORMULA(COUNTIF("'" & A2:A88, "*5*"))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.