Counting number of fields not blank

D

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.
 
M

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?
 
M

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.
 
G

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?
 
G

Gerry Kuta

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

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?
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top