Archive of Mr Excel Message Board
Back to Forms in Excel VBA archive index
Back to archive home
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
| Check out our Excel Resources
|
 |
 |
Re: count only cells with contents?
Posted by Mark W. on September 05, 2001 3:51 PM
Re: count only cells with contents?
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
Re: count only cells with contents?
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)
COUNT ignores empty cells...
Posted by Mark W. on September 05, 2001 4:00 PM
...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".
Aargh, sure does.
Posted by count me out. on September 05, 2001 4:10 PM
Re: COUNT ignores empty cells...
Posted by keke on September 05, 2001 4:14 PM
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
Re: count only cells with contents?
Posted by keke on September 05, 2001 4:19 PM
Yep. That worked. Seems simple now I look at it.
keke
Whoa...
Posted by Mark W. on September 05, 2001 4:23 PM
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:
This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our
online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.