How to count blank cells that contain formula?

[Guest]

I am trying to count blank cells in a column that contain a formula, but when
I do excel counts the formulas as well even though they are blank...

Currently my formula looks like:

=IF(B43=INDIRECT("Medicaid_Medical!"&I43&"$2"),COUNTA(OFFSET(INDIRECT("Medicaid_Medical!"&I43&"$2"),2,0,47,1))/COUNTA(States_Medical),0)

The problem is the cells in the column it is counting contain an if
statement to see what cells are blank. Any way to look over the blank cells
and keep the if statement?

 

[Guest]

I guess you can disregard that example formula, it doesn't give a good
picture. But i still would like to know how to count cells and ignore the
cells containing a function....

 

[Guest]

Let's say we want to count that are pseudo-blank. Formulas returning blank:

Enter this UDF:

Function fakeblank(r As Range) As Integer
Dim r1 As Range
For Each r1 In r
If r1.HasFormula Then
If Len(r1.Value) = 0 Then
fakeblank = fakeblank + 1
End If
End If
Next
End Function


use it like:

=fakeblank(A1:A50)

It will count cells containing stuff like:
=IF(1=1,"","")

If you are not familiar with UDFs, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Biff]

So, you want to count EMPTY cells and exclude cells with formula blanks?

=SUMPRODUCT(--(ISBLANK(A1:A10)))

Biff

 

[Guest]

how would i go about using this in my formula though?

 

[Guest]

So baiscally I am trying to find a percent for only the cells that have a
state name associated with them...

AK AK Medical
AL }
AR AR Medical } To the right of the state names lies other data but

AZ } only for the states with state names
CA
CO
CT CT Medical

I only want to find the percent of cells completed with State Names
associated. Additionally i would like it to reflect when state names are
added. Once the state names are added they will be included in the percent.

Im I making a good picture?