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