Prev: How do I use Excel to convert feet to kilometers?
Next: replace a single character WITH FORMAT in a cell
From: Phyllsf on 22 Feb 2010 19:56
I have two columns of data that I export from MS-Access into Excel and then
paste into another excel spreadsheet.

Column A is my description; Columns B and C are my values.

Both Column B and Column C have 50 rows. (This number varies)
Column B has some blank rows.
Column C has data in all rows (always).

I need to count the number of records in each column.

Using the CountA command, I currently first have to select any blank field
and delete the contents as Excel is reacting as if they are not blank.

Using the CountBlank command it correctly counts the blank fields, however,
I need to count the Non-Blank fields.

=COUNTA(B1:B50) would give me what I need if it would ignore the empty
fields.
=COUNTA(C1:C50) works fine.

(I've tried subtracting the blank count from the total in Column C but my
total fields are not directly following the populated fields so to easy to
have an extra blank row in the range.)

Perhaps this is an Access question? How do I make empty fields blank?

Thanks in advance.

From: T. Valko on 22 Feb 2010 20:20
Try checking one of those "blank" cells to see what's in it.

Let's assume B1 is one of those cells.

Try these formulas:

=LEN(B1)

=CODE(B1)

What results do you get wth those formulas?

--
Biff
Microsoft Excel MVP


"Phyllsf" <Phyllsf(a)discussions.microsoft.com> wrote in message
news:54488548-ADB1-4216-ABA9-45815A0BE791(a)microsoft.com...
>I have two columns of data that I export from MS-Access into Excel and then
> paste into another excel spreadsheet.
>
> Column A is my description; Columns B and C are my values.
>
> Both Column B and Column C have 50 rows. (This number varies)
> Column B has some blank rows.
> Column C has data in all rows (always).
>
> I need to count the number of records in each column.
>
> Using the CountA command, I currently first have to select any blank field
> and delete the contents as Excel is reacting as if they are not blank.
>
> Using the CountBlank command it correctly counts the blank fields,
> however,
> I need to count the Non-Blank fields.
>
> =COUNTA(B1:B50) would give me what I need if it would ignore the empty
> fields.
> =COUNTA(C1:C50) works fine.
>
> (I've tried subtracting the blank count from the total in Column C but my
> total fields are not directly following the populated fields so to easy to
> have an extra blank row in the range.)
>
> Perhaps this is an Access question? How do I make empty fields blank?
>
> Thanks in advance.
>


From: tompl on 22 Feb 2010 20:57
It will depend on the format of the field in Access. If it is formated
"number" then no value fields will export os "0". but if it is formated as
text then no value fields will export as blank.

Once in excel, the first scenario could be managed with:

=COUNTA(B2:B10)-COUNTIF(B2:B10,0)

The second scenario could be managed with:

=COUNTA(C2:C10)

The best solution could be to add a field in Access that meets your needs in
Excel.


 | 
Pages: 1
Prev: How do I use Excel to convert feet to kilometers?
Next: replace a single character WITH FORMAT in a cell