Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
IntricateFool
 
Posts: n/a
Default How to count blank cells that contain formula?

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"),COU NTA(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?

  #2   Report Post  
Posted to microsoft.public.excel.misc
IntricateFool
 
Posts: n/a
Default How to count blank cells that contain formula?

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

"IntricateFool" wrote:

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"),COU NTA(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?

  #3   Report Post  
Posted to microsoft.public.excel.misc
Gary''s Student
 
Posts: n/a
Default How to count blank cells that contain formula?

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

--
Gary's Student


"IntricateFool" wrote:

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"),COU NTA(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?

  #4   Report Post  
Posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default How to count blank cells that contain formula?

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

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

Biff

"IntricateFool" wrote in message
...
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"),COU NTA(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?



  #5   Report Post  
Posted to microsoft.public.excel.misc
IntricateFool
 
Posts: n/a
Default How to count blank cells that contain formula?

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

"Gary''s Student" wrote:

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

--
Gary's Student


"IntricateFool" wrote:

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"),COU NTA(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?



  #6   Report Post  
Posted to microsoft.public.excel.misc
IntricateFool
 
Posts: n/a
Default How to count blank cells that contain formula?

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?



"IntricateFool" wrote:

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

"Gary''s Student" wrote:

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

--
Gary's Student


"IntricateFool" wrote:

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"),COU NTA(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?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to count interstitial blank cells? [email protected] Excel Discussion (Misc queries) 3 April 16th 06 03:53 AM
How can I count conditional blank cells? Bruce Henson Excel Worksheet Functions 2 March 29th 06 04:10 PM
Match then lookup Tenacity Excel Worksheet Functions 9 December 3rd 05 06:30 AM
formula to count cells not blanK pmarques Excel Worksheet Functions 4 August 3rd 05 01:44 PM
Formula to count the cells in a range that have a fill color. Slainteva Excel Discussion (Misc queries) 2 January 19th 05 09:25 PM


All times are GMT +1. The time now is 04:22 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"