Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to count interstitial blank cells? | Excel Discussion (Misc queries) | |||
How can I count conditional blank cells? | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
formula to count cells not blanK | Excel Worksheet Functions | |||
Formula to count the cells in a range that have a fill color. | Excel Discussion (Misc queries) |