Count cells with numbers in Excel

When working in Excel, you often need to determine how many cells in a range contain numbers. This can be useful for cleaning imported datasets, validating calculations, or ensuring your analysis is based only on numeric values. While Excel provides built-in functions to handle such tasks, !SEMTools makes the process faster and more flexible.

Count numeric cells (containing only numbers)

If you want to count the number of cells in a range that contain only numeric values (without text or logical values), the easiest method is to use the COUNT function. For example:

=COUNT(A2:A6)

This formula returns the number of cells that contain numeric values in the specified range. Empty cells, text, and error values are ignored.

Alternatively, you can achieve the same result with an array formula that works without relying on the COUNT function:

=SUM(1-ISERROR(SIGN(A2:A6)))

Here, SIGN returns 1 for positive numbers, -1 for negative numbers, and 0 for zero, while producing an error for non-numeric values. ISERROR detects those errors, and 1-ISERROR(...) converts TRUE/FALSE into 0/1. Finally, SUM counts the numeric entries. Two differences from COUNT: an empty cell gives SIGN(…) = 0, so it is counted as a number, and a number stored as text is converted by SIGN and counted too. On a range without blanks and text numbers, both formulas agree.

Counting Cells that contain numbers with !SEMTools

If you managed to find cells with numbers, then it's quite easy to count them. But !SEMTools makes the process faster and more flexible. In the “Extract” → “Cells” → “Containing” menu, you can choose to keep only the cells with numbers, clearing all others instantly. This means no extra formulas, no helper columns, and no manual filtering — perfect for preparing your dataset in seconds.

See also:

• LEN function
• SUBSTITUTE function
• COUNTIF function