Count of Non Formula Cells in a column-CodePudding

lets say you had formulas in each cell between A1 & A9 but A10 had a hard coded number. Within VBA is there a formula you could enter in A11 that would give you the number 1 due to one record in the column having a value instead of a formula. (Or even a "False" would work in "A11" showing that column had 1 fail)?

CodePudding user response：

Try 1 of the array formulas below (Validated with Ctrl Enter):

• Number of cells containing a formula:

=COUNT(IF(ISFORMULA(A1:A10),A1:A10))

• Number of cells NOT containing a formula (including empty cells):

=COUNT(IF(NOT(ISFORMULA(A1:A10)),A1:A10))

• Number of cells NOT containing a formula (excluding empty cells):

=COUNT(IF((NOT(ISBLANK(A1:A10))*NOT(ISFORMULA(A1:A10)))>0,A1:A10))

Note you can also use the previous formula and COUNTBLANK

=COUNT(IF(NOT(ISFORMULA(A1:A10)),A1:A10)) - COUNTBLANK(A1:A10)

• First row with a value instead of a formula:

=MIN(IF((NOT(ISBLANK(A1:A10))*NOT(ISFORMULA(A1:A10)))>0,ROW(A1:A10)))