Count Function in Excel

  • Thread starter Thread starter GregS
  • Start date Start date
G

GregS

Hello,

I am attempting to use the: COUNT and COUNTIF to count a
Column for all rows where the Value IS NOT NULL, Does
Excel recognize NULL, the function I am using is:

=COUNTIF('EE Master'!A2:A2010,('EE Master'!A2:A2010<>""))

Thanks,

Greg
 
I am attempting to use the: COUNT and COUNTIF to count a
Column for all rows where the Value IS NOT NULL, Does
Excel recognize NULL, the function I am using is:

=COUNTIF('EE Master'!A2:A2010,('EE Master'!A2:A2010<>""))
Click to expand...

Improper syntax. You sould use

=COUNTIF('EE Master'!A2:A2010,"<>")

but it may not give you the results you expect since it includes cells
containing text constants or formulas evaluating to "" in the count.

How do you define 'NULL'? #NULL! is an Excel error value, and it's unreasonable
to figure that such error values are 'NULL'. You could also mean blank, as in
cells with no contents at all for which ISBLANK returns True and COUNT returns
0. Or you could mean either blank or text evaluating to "", zero-length strings
which are also sometimes referred to as empty strings. Precise terminology,
though often perceived as tedious, is nevertheless essential. 'NULL' is
ambiguous in Excel, and ambiguity is almost always a source of nasty problems.

To count all cells that aren't truly blank in the ISBLANK sense, just use

=COUNTA('EE Master'!A2:A2010)

To count all cells that aren't blank or "", use

=SUMPRODUCT(--('EE Master'!A2:A2010<>""))
 
Whoops, you don't want to count blanks, you want to count nonblanks.

Salvaging my suggestion, you could use

=ROWS(2:2010)-COUNTBLANK('EE Master'!A2:A2010)
 
Thanks,

I'm using this as ( Total rows in Column - (=COUNTBLANK
('EE Master'!A2:A2010))to get all rows where the Value IS
NOT NULL.
 
Glad you caught that..

Also I assume "Total rows in column" actually means "total rows in range"..
 
Back
Top