MrExcel Publishing
Your One Stop for Excel Tips & Solutions

count only cells with contents?


Posted by keke on September 05, 2001 3:46 PM

Hi, folks. Still messing with the stats on this production tracking system I'm putting together. Here's the latest quandary:
Column "O" contains the date a job is completed. If the job is still outstanding the cell in that row for column O is blank. I need to keep a running total of jobs completed.

There is a function to look for blank cells. I want Excel to do the opposite: look for cells with contents only, and then count those cells. I was thinking along the lines of combining COUNTIF and ISNUMBER but I can't seem to hit the right combination. Or maybe I'm completely toast. :-)

Ideas?

keke


Posted by Mark W. on September 05, 2001 3:51 PM

=COUNT(O:O)

Posted by IML on September 05, 2001 3:55 PM

How about
=COUNTIF(O1:O12,">"&0)
if you are populating no dates with a "" (ie not really blank)
other wise you could use
=COUNT(O1:O12)-ISBLANK(O1:O12)

good luck

Posted by IML on September 05, 2001 3:57 PM

Ignore that last one...not thinking to well. but the first one may work (again if they aren't truly blank)

Posted by Mark W. on September 05, 2001 4:00 PM

COUNT ignores empty cells...

...see the Help Topic for COUNT worksheet function
which states, "If an argument is an array or
reference, only numbers in that array or reference
are counted. Empty cells, logical values, text, or
error values in the array or reference are ignored".

Posted by count me out. on September 05, 2001 4:10 PM

Aargh, sure does.

Posted by keke on September 05, 2001 4:14 PM

Re: COUNT ignores empty cells...

Mark said:

That's true, but a date doesn't seem to be a true number, because it returns a zero if I enter =COUNT(O2:O100).

IML suggested:

That didn't work either. Still zeros. Do dates get read by Excel as *text* or some other weird thing?

keke

Posted by keke on September 05, 2001 4:19 PM


Yep. That worked. Seems simple now I look at it.

keke

Posted by Mark W. on September 05, 2001 4:23 PM

Whoa...

An Excel date (not a text representation of a date
such as "December") is a number. See the Excel
Help topic for "Tips on entering dates and times"
which states, "Microsoft Excel treats dates and
times as numbers". Make sure that your column
isn't formatted using the "Text" format, and that
your dates have been entered based in one of the
accepted date models (e.g., m/d/yy, d-mmm-yy, etc.) Mark said: