Another count issue

[Ed Davis]

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

 

[Guest]

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:

 

[Gord Dibben]

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

 

[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]

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 Tools>Options>View.

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


Gord

 

[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.

 

[Ed Davis]

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

 

[Gord Dibben]

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

 

[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]

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