This formula will count only numbers <> 0, excluding blanks, error messages, etc. BUT it will not exclude the cells that display ###### (but really contain a number) if the reason for that is a column that is too narrow, or a negative date or time value.
=SUMPRODUCT(--ISNUMBER(A2:A200))-COUNTIF(A2:A200,0)
If you really want to avoid counting cells that display ####### when the underlying contents is a number not equal to zero, you will need to use a UDF to act on the Text property of the cell. In addition, narrowing or widening the column to produce that affect will not trigger a calculation event that would update the formula, so you need to somehow do that in order to ensure the formula results are correct.
That is why I added Application.Volatile to the code, but it is still possible to produce a situation where the result of the formula does not agree with the display in the range being checked, at least until the next calculation event takes place.
To enter this User Defined Function (UDF), alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.
To use this User Defined Function (UDF), enter a formula like
=CountNumbersNEZero(A2:A200)
in some cell.
Option Explicit
Function CountNumbersNEZero(rg As Range) As Long
Application.Volatile
Dim C As Range
Dim L As Double
For Each C In rg
If IsNumeric(C.Text) Then
If C.Text <> 0 Then L = L + 1
End If
Next C
CountNumbersNEZero = L
End Function