# Count cells with an integer in them as substring

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`.

• 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. Feb 16 at 3:04

## 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*"))
``````