software

Here's what I'm attempting to do: For each column, X,Y, Z, I am attempting
to count nonblanks. However, the data was imported from Access and Oracle,
and Excel treats what appear to be blank cells as nonblanks. I've tested
this theory by highlighting a couple of quot;blankquot; cells and deleting them, and
my count changes. So, can I get Excel to put a value into my quot;blankquot; cells,
so then I could filter it out, or create a formula that would only count
dates in my columns (which is what I'm after).

This is what I'm looking at:

A B C
1 2/4/2006 2/6/2006 (quot;blankquot;)
2 (quot;blankquot;) 12/13/2005 1/7/2006
3 2/20/2006 1/15/2006 (quot;blankquot;)

In each column if I use a COUNTA I'll get a total of 3, instead of 2 for A,
3 for B and 1 for C. Any ideas?Countblank(range)
actually does count blank as well as empty cells

quot;JackieDquot; wrote:

gt; Here's what I'm attempting to do: For each column, X,Y, Z, I am attempting
gt; to count nonblanks. However, the data was imported from Access and Oracle,
gt; and Excel treats what appear to be blank cells as nonblanks. I've tested
gt; this theory by highlighting a couple of quot;blankquot; cells and deleting them, and
gt; my count changes. So, can I get Excel to put a value into my quot;blankquot; cells,
gt; so then I could filter it out, or create a formula that would only count
gt; dates in my columns (which is what I'm after).
gt;
gt; This is what I'm looking at:
gt;
gt; A B C
gt; 1 2/4/2006 2/6/2006 (quot;blankquot;)
gt; 2 (quot;blankquot;) 12/13/2005 1/7/2006
gt; 3 2/20/2006 1/15/2006 (quot;blankquot;)
gt;
gt; In each column if I use a COUNTA I'll get a total of 3, instead of 2 for A,
gt; 3 for B and 1 for C. Any ideas?
gt;

I don't want Excel to count my quot;blanksquot;. I want it to ignore them. I only
want to count cells that have dates in them.

quot;bjquot; wrote:

gt; Countblank(range)
gt; actually does count blank as well as empty cells
gt;
gt; quot;JackieDquot; wrote:
gt;
gt; gt; Here's what I'm attempting to do: For each column, X,Y, Z, I am attempting
gt; gt; to count nonblanks. However, the data was imported from Access and Oracle,
gt; gt; and Excel treats what appear to be blank cells as nonblanks. I've tested
gt; gt; this theory by highlighting a couple of quot;blankquot; cells and deleting them, and
gt; gt; my count changes. So, can I get Excel to put a value into my quot;blankquot; cells,
gt; gt; so then I could filter it out, or create a formula that would only count
gt; gt; dates in my columns (which is what I'm after).
gt; gt;
gt; gt; This is what I'm looking at:
gt; gt;
gt; gt; A B C
gt; gt; 1 2/4/2006 2/6/2006 (quot;blankquot;)
gt; gt; 2 (quot;blankquot;) 12/13/2005 1/7/2006
gt; gt; 3 2/20/2006 1/15/2006 (quot;blankquot;)
gt; gt;
gt; gt; In each column if I use a COUNTA I'll get a total of 3, instead of 2 for A,
gt; gt; 3 for B and 1 for C. Any ideas?
gt; gt;

When I do importing of data, I usually find that I must quot;cleanquot; the data.
That is, get rid of undesirable and unseen characters. Cell-View will tell
me what is in the cells, and Edit gt; Replace will convert them to
quot;nothingquot;.....or quot;ASAP Utilitiesquot; has a feature that will help.

hth
Vaya con Dios,
Chuck, CABGx3quot;JackieDquot; gt; wrote in message
...
gt; Here's what I'm attempting to do: For each column, X,Y, Z, I am
attempting
gt; to count nonblanks. However, the data was imported from Access and
Oracle,
gt; and Excel treats what appear to be blank cells as nonblanks. I've tested
gt; this theory by highlighting a couple of quot;blankquot; cells and deleting them,
and
gt; my count changes. So, can I get Excel to put a value into my quot;blankquot;
cells,
gt; so then I could filter it out, or create a formula that would only count
gt; dates in my columns (which is what I'm after).
gt;
gt; This is what I'm looking at:
gt;
gt; A B C
gt; 1 2/4/2006 2/6/2006 (quot;blankquot;)
gt; 2 (quot;blankquot;) 12/13/2005 1/7/2006
gt; 3 2/20/2006 1/15/2006 (quot;blankquot;)
gt;
gt; In each column if I use a COUNTA I'll get a total of 3, instead of 2 for
A,
gt; 3 for B and 1 for C. Any ideas?
gt;
Try using COUNT instead of COUNTA.....

Vaya con Dios,
Chuck, CABGx3quot;JackieDquot; gt; wrote in message
...
gt; I don't want Excel to count my quot;blanksquot;. I want it to ignore them. I only
gt; want to count cells that have dates in them.
gt;
gt; quot;bjquot; wrote:
gt;
gt; gt; Countblank(range)
gt; gt; actually does count blank as well as empty cells
gt; gt;
gt; gt; quot;JackieDquot; wrote:
gt; gt;
gt; gt; gt; Here's what I'm attempting to do: For each column, X,Y, Z, I am
attempting
gt; gt; gt; to count nonblanks. However, the data was imported from Access and
Oracle,
gt; gt; gt; and Excel treats what appear to be blank cells as nonblanks. I've
tested
gt; gt; gt; this theory by highlighting a couple of quot;blankquot; cells and deleting
them, and
gt; gt; gt; my count changes. So, can I get Excel to put a value into my quot;blankquot;
cells,
gt; gt; gt; so then I could filter it out, or create a formula that would only
count
gt; gt; gt; dates in my columns (which is what I'm after).
gt; gt; gt;
gt; gt; gt; This is what I'm looking at:
gt; gt; gt;
gt; gt; gt; A B C
gt; gt; gt; 1 2/4/2006 2/6/2006 (quot;blankquot;)
gt; gt; gt; 2 (quot;blankquot;) 12/13/2005 1/7/2006
gt; gt; gt; 3 2/20/2006 1/15/2006 (quot;blankquot;)
gt; gt; gt;
gt; gt; gt; In each column if I use a COUNTA I'll get a total of 3, instead of 2
for A,
gt; gt; gt; 3 for B and 1 for C. Any ideas?
gt; gt; gt;

The only problem with COUNTBLANK is that if your imported data is
creating spaces in the cells where it appears to be quot;blankquot;, COUNTBLANK
will ignore those cells. When you select one of the cells, is there a
single apostrophe in the formula bar? If you type in a single
apostrophe on a blank sheet you'll see that it does not appear in the
cell but it does in the formula bar. If not than my guess is it is
probably spaces in the quot;blankquot; cells that are your problem. Try using
just the COUNT function, not COUNTA to get the number of dates, not
blanks. COUNT counts numbers which it recognizes dates as. It will
not count text or spaces etc. I just tried this using both the space
in a cell of my range and an apostrophe and came up with the right
number of cells that contained dates rather than blanks.

Does that help?

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=515051See if one of these works for you:

If you want to clean the quot;blankquot; cells, try this:
This works for me (and I don't know why)

Select the data range
Editgt;Find
Don't enter anything in the Find What box.
Click the [Find All] button
(That should display a list of all of the cells you are interested in)
Hold down the [Ctrl] key and press the letter A
(That should select all of the matching cells)
Click the [Close] button to close the window

Press the [Delete] key to clear those cells.

OR

Otherwise, since Excel stores dates as numbers, just use the COUNT function
to count the dates.

Does either of those help?

***********
Regards,
Ron

XL2002, WinXP-Proquot;JackieDquot; wrote:

gt; Here's what I'm attempting to do: For each column, X,Y, Z, I am attempting
gt; to count nonblanks. However, the data was imported from Access and Oracle,
gt; and Excel treats what appear to be blank cells as nonblanks. I've tested
gt; this theory by highlighting a couple of quot;blankquot; cells and deleting them, and
gt; my count changes. So, can I get Excel to put a value into my quot;blankquot; cells,
gt; so then I could filter it out, or create a formula that would only count
gt; dates in my columns (which is what I'm after).
gt;
gt; This is what I'm looking at:
gt;
gt; A B C
gt; 1 2/4/2006 2/6/2006 (quot;blankquot;)
gt; 2 (quot;blankquot;) 12/13/2005 1/7/2006
gt; 3 2/20/2006 1/15/2006 (quot;blankquot;)
gt;
gt; In each column if I use a COUNTA I'll get a total of 3, instead of 2 for A,
gt; 3 for B and 1 for C. Any ideas?
gt;

What commands do I use to tell Excel to only count the cells that have a
number in date format?

quot;CLRquot; wrote:

gt; Try using COUNT instead of COUNTA.....
gt;
gt; Vaya con Dios,
gt; Chuck, CABGx3
gt;
gt;
gt; quot;JackieDquot; gt; wrote in message
gt; ...
gt; gt; I don't want Excel to count my quot;blanksquot;. I want it to ignore them. I only
gt; gt; want to count cells that have dates in them.
gt; gt;
gt; gt; quot;bjquot; wrote:
gt; gt;
gt; gt; gt; Countblank(range)
gt; gt; gt; actually does count blank as well as empty cells
gt; gt; gt;
gt; gt; gt; quot;JackieDquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Here's what I'm attempting to do: For each column, X,Y, Z, I am
gt; attempting
gt; gt; gt; gt; to count nonblanks. However, the data was imported from Access and
gt; Oracle,
gt; gt; gt; gt; and Excel treats what appear to be blank cells as nonblanks. I've
gt; tested
gt; gt; gt; gt; this theory by highlighting a couple of quot;blankquot; cells and deleting
gt; them, and
gt; gt; gt; gt; my count changes. So, can I get Excel to put a value into my quot;blankquot;
gt; cells,
gt; gt; gt; gt; so then I could filter it out, or create a formula that would only
gt; count
gt; gt; gt; gt; dates in my columns (which is what I'm after).
gt; gt; gt; gt;
gt; gt; gt; gt; This is what I'm looking at:
gt; gt; gt; gt;
gt; gt; gt; gt; A B C
gt; gt; gt; gt; 1 2/4/2006 2/6/2006 (quot;blankquot;)
gt; gt; gt; gt; 2 (quot;blankquot;) 12/13/2005 1/7/2006
gt; gt; gt; gt; 3 2/20/2006 1/15/2006 (quot;blankquot;)
gt; gt; gt; gt;
gt; gt; gt; gt; In each column if I use a COUNTA I'll get a total of 3, instead of 2
gt; for A,
gt; gt; gt; gt; 3 for B and 1 for C. Any ideas?
gt; gt; gt; gt;
gt;
gt;
gt;

COUNT

Vaya con Dios,
Chuck, CABGx3quot;JackieDquot; gt; wrote in message
...
gt; What commands do I use to tell Excel to only count the cells that have a
gt; number in date format?
gt;
gt; quot;CLRquot; wrote:
gt;
gt; gt; Try using COUNT instead of COUNTA.....
gt; gt;
gt; gt; Vaya con Dios,
gt; gt; Chuck, CABGx3
gt; gt;
gt; gt;
gt; gt; quot;JackieDquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I don't want Excel to count my quot;blanksquot;. I want it to ignore them. I
only
gt; gt; gt; want to count cells that have dates in them.
gt; gt; gt;
gt; gt; gt; quot;bjquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Countblank(range)
gt; gt; gt; gt; actually does count blank as well as empty cells
gt; gt; gt; gt;
gt; gt; gt; gt; quot;JackieDquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Here's what I'm attempting to do: For each column, X,Y, Z, I am
gt; gt; attempting
gt; gt; gt; gt; gt; to count nonblanks. However, the data was imported from Access
and
gt; gt; Oracle,
gt; gt; gt; gt; gt; and Excel treats what appear to be blank cells as nonblanks. I've
gt; gt; tested
gt; gt; gt; gt; gt; this theory by highlighting a couple of quot;blankquot; cells and deleting
gt; gt; them, and
gt; gt; gt; gt; gt; my count changes. So, can I get Excel to put a value into my
quot;blankquot;
gt; gt; cells,
gt; gt; gt; gt; gt; so then I could filter it out, or create a formula that would only
gt; gt; count
gt; gt; gt; gt; gt; dates in my columns (which is what I'm after).
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; This is what I'm looking at:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; A B C
gt; gt; gt; gt; gt; 1 2/4/2006 2/6/2006 (quot;blankquot;)
gt; gt; gt; gt; gt; 2 (quot;blankquot;) 12/13/2005 1/7/2006
gt; gt; gt; gt; gt; 3 2/20/2006 1/15/2006 (quot;blankquot;)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; In each column if I use a COUNTA I'll get a total of 3, instead of
2
gt; gt; for A,
gt; gt; gt; gt; gt; 3 for B and 1 for C. Any ideas?
gt; gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;
That did it!!! Thanks!

quot;Ron Coderrequot; wrote:

gt; See if one of these works for you:
gt;
gt; If you want to clean the quot;blankquot; cells, try this:
gt; This works for me (and I don't know why)
gt;
gt; Select the data range
gt; Editgt;Find
gt; Don't enter anything in the Find What box.
gt; Click the [Find All] button
gt; (That should display a list of all of the cells you are interested in)
gt; Hold down the [Ctrl] key and press the letter A
gt; (That should select all of the matching cells)
gt; Click the [Close] button to close the window
gt;
gt; Press the [Delete] key to clear those cells.
gt;
gt; OR
gt;
gt; Otherwise, since Excel stores dates as numbers, just use the COUNT function
gt; to count the dates.
gt;
gt; Does either of those help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;JackieDquot; wrote:
gt;
gt; gt; Here's what I'm attempting to do: For each column, X,Y, Z, I am attempting
gt; gt; to count nonblanks. However, the data was imported from Access and Oracle,
gt; gt; and Excel treats what appear to be blank cells as nonblanks. I've tested
gt; gt; this theory by highlighting a couple of quot;blankquot; cells and deleting them, and
gt; gt; my count changes. So, can I get Excel to put a value into my quot;blankquot; cells,
gt; gt; so then I could filter it out, or create a formula that would only count
gt; gt; dates in my columns (which is what I'm after).
gt; gt;
gt; gt; This is what I'm looking at:
gt; gt;
gt; gt; A B C
gt; gt; 1 2/4/2006 2/6/2006 (quot;blankquot;)
gt; gt; 2 (quot;blankquot;) 12/13/2005 1/7/2006
gt; gt; 3 2/20/2006 1/15/2006 (quot;blankquot;)
gt; gt;
gt; gt; In each column if I use a COUNTA I'll get a total of 3, instead of 2 for A,
gt; gt; 3 for B and 1 for C. Any ideas?
gt; gt;