Google Sheets Formulas – Counting Decimal Values with ArrayFormula

formulasgoogle sheetsgoogle-sheets-arrayformula

I am trying to count the total cells that have a single decimal value.

For example:

1.1 (1)
2
1.2 (2)
3.333
1.3 (3)
4
1.4 (4)
5.55555
1.5 (5)
6
1.6 (6)
1.7777777
1.7 (7)
8
1.8 (8)
9.999999999
1.9 (9)

What formula should be used to attain a total of nine?

Best Answer

You can use the following

=ArrayFormula(COUNTIF(LEN(INDEX(SPLIT(A2:A,"."),,2)),"=1"))

Functions used:

Short answer

Instead of SEARCH use REGEXMATCH and JOIN.

Explanation

SEARCH is for searching partial strings into other strings and if it will be used with arrays, both parameters should be arrays of the same size.

JOIN could be used to create a regular expression to be used by REGEXMATCH which in turn will return a array of TRUE/FALSE if the first parameter is an array and it is nested inside a ARRAYFORMULA.

Considering the OP examples, the formula to be used is

=ArrayFormula(REGEXMATCH(E1:E4, JOIN("|",{"Tim","Frank"})))

Google Sheets – How to Skip Empty Cells with Consecutive Numbering Using ArrayFormula

Try

=ARRAYFORMULA(IFERROR(MATCH(A2:A12&ROW(A2:A12),FILTER(A2:A12&ROW(A2:A12),A2:A12<>""),0)))

Best Answer

Related Solutions

Google-sheets – How to check if a cell contains any of an array of values

Short answer

Explanation

Google Sheets – How to Skip Empty Cells with Consecutive Numbering Using ArrayFormula

Related Topic