How would I use count when a formula is in cells but if the cell has not
data except the formula how would I use count to count only the cells that
have a number and disregard the cells with just the formula.

Thanks in advance.

Ed Davis

Function myCount(myRange)
Application.Volatile
For Each c In myRange
If IsNumeric(c) And c.HasFormula = False And c < "" Then myCount = myCount
+ 1
Next
End Function

Put in regular module and use it :
=myCount(A1:A5)



"Ed Davis" skrev:

How would I use count when a formula is in cells but if the cell has not
data except the formula how would I use count to count only the cells that
have a number and disregard the cells with just the formula.

Thanks in advance.

Ed Davis

Ed

What do the formula cells return if there is not data to compute?

i.e. a null string like "" which COUNT would ignore or something else?


Gord Dibben MS Excel MVP

On Sat, 5 May 2007 04:34:51 -0400, "Ed Davis" wrote:

How would I use count when a formula is in cells but if the cell has not
data except the formula how would I use count to count only the cells that
have a number and disregard the cells with just the formula.

Thanks in advance.

Ed Davis

It is a link to whatever is in a cell from another sheet. So there is
nothing in the cell except the link.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Ed

What do the formula cells return if there is not data to compute?

i.e. a null string like "" which COUNT would ignore or something else?


Gord Dibben MS Excel MVP

On Sat, 5 May 2007 04:34:51 -0400, "Ed Davis"
wrote:

How would I use count when a formula is in cells but if the cell has not
data except the formula how would I use count to count only the cells that
have a number and disregard the cells with just the formula.

Thanks in advance.

Ed Davis

A link such as =Sheet2!A1 should give you a zero if nothing in Sheet2!A1

Unless you have trapped for that like =IF(Sheet2!A1="","",Sheet2!A1)

Or you have disabled zeros view in ToolsOptionsView.

Can you post the exact content of the "links" formulas.


Gord

On Sat, 5 May 2007 15:39:37 -0400, "Ed Davis" wrote:

It is a link to whatever is in a cell from another sheet. So there is
nothing in the cell except the link.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Ed

What do the formula cells return if there is not data to compute?

i.e. a null string like "" which COUNT would ignore or something else?


Gord Dibben MS Excel MVP

On Sat, 5 May 2007 04:34:51 -0400, "Ed Davis"
wrote:

How would I use count when a formula is in cells but if the cell has not
data except the formula how would I use count to count only the cells that
have a number and disregard the cells with just the formula.

Thanks in advance.

Ed Davis

You are correct I forgot about the options for the zeros.
So that means I need to disregard the cells with a zero.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
A link such as =Sheet2!A1 should give you a zero if nothing in Sheet2!A1

Unless you have trapped for that like =IF(Sheet2!A1="","",Sheet2!A1)

Or you have disabled zeros view in ToolsOptionsView.

Can you post the exact content of the "links" formulas.


Gord

On Sat, 5 May 2007 15:39:37 -0400, "Ed Davis"
wrote:

It is a link to whatever is in a cell from another sheet. So there is
nothing in the cell except the link.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
Ed

What do the formula cells return if there is not data to compute?

i.e. a null string like "" which COUNT would ignore or something
else?


Gord Dibben MS Excel MVP

On Sat, 5 May 2007 04:34:51 -0400, "Ed Davis"
wrote:

How would I use count when a formula is in cells but if the cell has not
data except the formula how would I use count to count only the cells
that
have a number and disregard the cells with just the formula.

Thanks in advance.

Ed Davis

The link looks like this "Sheet1:a4"
It does display a zero if nothing is in cel "Sheet1:a4".


"Ed Davis" wrote in message
...
You are correct I forgot about the options for the zeros.
So that means I need to disregard the cells with a zero.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
A link such as =Sheet2!A1 should give you a zero if nothing in Sheet2!A1

Unless you have trapped for that like =IF(Sheet2!A1="","",Sheet2!A1)

Or you have disabled zeros view in ToolsOptionsView.

Can you post the exact content of the "links" formulas.


Gord

On Sat, 5 May 2007 15:39:37 -0400, "Ed Davis"
wrote:

It is a link to whatever is in a cell from another sheet. So there is
nothing in the cell except the link.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Ed

What do the formula cells return if there is not data to compute?

i.e. a null string like "" which COUNT would ignore or something
else?


Gord Dibben MS Excel MVP

On Sat, 5 May 2007 04:34:51 -0400, "Ed Davis"
wrote:

How would I use count when a formula is in cells but if the cell has
not
data except the formula how would I use count to count only the cells
that
have a number and disregard the cells with just the formula.

Thanks in advance.

Ed Davis

I don't think so Ed.

Look again......probably the : is a ! and is preceded by an = sign.

=Sheet1!A4

When posting things like this it is best to copy straight from the formula bar
then paste into your post rather than rely on your eyes or not making typing
errors.

Replace it with a formula similar to one I posted below which returns a null
string which Excel will ignore in your =COUNT(range) formula.


Gord

On Sat, 5 May 2007 20:45:43 -0400, "Ed Davis" wrote:

The link looks like this "Sheet1:a4"
It does display a zero if nothing is in cel "Sheet1:a4".


"Ed Davis" wrote in message
...
You are correct I forgot about the options for the zeros.
So that means I need to disregard the cells with a zero.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
A link such as =Sheet2!A1 should give you a zero if nothing in Sheet2!A1

Unless you have trapped for that like =IF(Sheet2!A1="","",Sheet2!A1)

Or you have disabled zeros view in ToolsOptionsView.

Can you post the exact content of the "links" formulas.


Gord

On Sat, 5 May 2007 15:39:37 -0400, "Ed Davis"
wrote:

It is a link to whatever is in a cell from another sheet. So there is
nothing in the cell except the link.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
m...
Ed

What do the formula cells return if there is not data to compute?

i.e. a null string like "" which COUNT would ignore or something
else?


Gord Dibben MS Excel MVP

On Sat, 5 May 2007 04:34:51 -0400, "Ed Davis"
wrote:

How would I use count when a formula is in cells but if the cell has
not
data except the formula how would I use count to count only the cells
that
have a number and disregard the cells with just the formula.

Thanks in advance.

Ed Davis

I figured it out and everything working fine now. I just had to use COUNTIF
and it worked fine.

Thank you everyone for your help.

I have learned a lot from these posting.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
I don't think so Ed.

Look again......probably the : is a ! and is preceded by an = sign.

=Sheet1!A4

When posting things like this it is best to copy straight from the formula
bar
then paste into your post rather than rely on your eyes or not making
typing
errors.

Replace it with a formula similar to one I posted below which returns a
null
string which Excel will ignore in your =COUNT(range) formula.


Gord

On Sat, 5 May 2007 20:45:43 -0400, "Ed Davis"
wrote:

The link looks like this "Sheet1:a4"
It does display a zero if nothing is in cel "Sheet1:a4".


"Ed Davis" wrote in message
...
You are correct I forgot about the options for the zeros.
So that means I need to disregard the cells with a zero.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
A link such as =Sheet2!A1 should give you a zero if nothing in
Sheet2!A1

Unless you have trapped for that like =IF(Sheet2!A1="","",Sheet2!A1)

Or you have disabled zeros view in ToolsOptionsView.

Can you post the exact content of the "links" formulas.


Gord

On Sat, 5 May 2007 15:39:37 -0400, "Ed Davis"
wrote:

It is a link to whatever is in a cell from another sheet. So there is
nothing in the cell except the link.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
om...
Ed

What do the formula cells return if there is not data to compute?

i.e. a null string like "" which COUNT would ignore or something
else?


Gord Dibben MS Excel MVP

On Sat, 5 May 2007 04:34:51 -0400, "Ed Davis"
wrote:

How would I use count when a formula is in cells but if the cell has
not
data except the formula how would I use count to count only the cells
that
have a number and disregard the cells with just the formula.

Thanks in advance.

Ed Davis

Just to clarify.........

=IF(Sheet2!A1="","",Sheet2!A1) entered in a cell on Sheet1 states "If

Sheet2!A1 has no value then show nothing in the cell with the formula but if

Sheet2!A1 has a value, show that value here"


Gord

On Sat, 05 May 2007 19:34:08 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Replace it with a formula similar to one I posted below which returns a null
string which Excel will ignore in your =COUNT(range) formula.