Counting cells which have some numerical value...

  • Thread starter Thread starter Spin
  • Start date Start date
S

Spin

Gurus,

In Excel, how do I "count" a value of a cell as "1" if it has a numerical
value inside (for example, 2.50), and count it as "0" if the cell is blank,
such that at the bottom of the column I will have a total count of all
non-blank cells?
 
=count()
will count all the cells with numbers

=counta()
will count all the cells with anything--including formulas that evaluate to ""

=countblank()
will count the number of blank cells--including the formulas that evaluate to ""
 
Assuming the cells you want to check run from A1 to A100...

=COUNT(A1:A100)

This will return the count of cells with numbers (or dates, since they are
floating point numbers underneath it all)... it won't count cells with
non-numbers in the (blank cells or cells with text in them).
 
To count *only* numbers, meaning text and blank cells are excluded:

=COUNT(A1:A50)

If you want to *include* TEXT cells in the count, which also counts zero
length text ( "" ) blank appearing cells:

=COUNTA(A1:A50)

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Gurus,

In Excel, how do I "count" a value of a cell as "1" if it has a numerical
value inside (for example, 2.50), and count it as "0" if the cell is blank,
such that at the bottom of the column I will have a total count of all
non-blank cells?
 
COUNTA counts all non blank cells including those that look blank with
formulas
COUNT count cells with numbers
COUNTBLANK count empty/blank cells except blanks from formulas

--


Regards,


Peo Sjoblom
 
You could try this:

=COUNTIF(A:A,">0")

Counts all cells in column A greater than zero (blanks being treated
as a zero in this case).

Hope this helps.

Pete
 
Back
Top