Counting number of fields not blank

[mika.]

How would I count the number of fields in a column that
are not blank?

Thanks!

 

[Dave Ramage]

=COUNTIF(A:A,"")

This will only include cells within the used range, which
should suit your purpose. You may want to put a more
specific cell reference in if you have cells filled in
below the data table (i.e something like =COUNTIF
(A1:A5000,"")

Cheers,
Dave.

 

[Gerry Kuta]

=COUNT(IF(A1:Ax>0,A1:Ax))array entered (Ctrl:Shift:Enter)

 

[mika.]

=COUNTIF(Q3:Q518,"")
....seems to count the cells that are blank. I would like
to count the number of cells that are not blank (without
having to subtract the cells that are blank from the total
number of cells...any suggestions?

 

[mika.]

Hi Gerry,

When I typed in this:
=COUNT(IF(Q3:Q518>0,Q1:Q518))
it did not increment whether I had all my cells completely
blank or had some cells that were not blank...any
suggestions?

Thanks.

 

[Gerry Kuta]

If I understand your question, you'd like to count cell
that are not blank?
If you had 10, 0, 20, 30, 0, blank... to the bottom of
your column the formula results in a number 3. Replace the

0 with >=0 and it gives you 5. Are either one of those

what you need?

 

[Gerry Kuta]

Did you put the 0 in A6?
The formula should reside somewhere outside of the range
of numbers you are referencing.

 

[mika.]

I did not put 0 in a6..the formula I(=COUNT(IF
(A1:A5>=0,A1:A5))) had in a6 gave me 0...any ideas?

 

[Alan Beban]

=COUNTIF(Range,"<>") and =COUNTA(Range)-COUNTBLANK(Range) don't return
the same value.

=COUNTIF(Range,"<>") and =SUMPRODUCT(1-ISBLANK(Range)) each exclude only
truly blank cells.

=COUNTA(Range)-COUNTBLANK(Range) double-excludes the truly blank cells.

Otherwise, the information's great.

Instead of =COUNTA(Range)-COUNTBLANK(Range) one could use
=COUNTA(Range)+COUNTIF(Range,"=")-COUNTBLANK(Range) or
=(ROWS(Range)*(COLUMNS(Range))-COUNTBLANK(Range))

Alan Beban