Counting number of fields not blank

  • Thread starter Thread starter mika.
  • Start date Start date
=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.
 
=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?
 
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.
 
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
Click to expand...
what you need?
 
Did you put the 0 in A6?
The formula should reside somewhere outside of the range
of numbers you are referencing.
 
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?
 
=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
 
Back
Top