Countif based on formula in cell, not value?

[fishoutofwater]

Hi, I have a really complex formula in each cell a1 through a10. I want to make sure I got everything in it I need at the formula level.... so let's say in A1 I have:

=if((f5=7),"hi bob",$f$7)

Is there a formula I can put in b1 that will count the instances of f1 in A1. I know how to do things like =countif, but that countif works on the value level. How can I do a countif on the formula level to tell me how many "f7" 's are in the formula in a1?

Thanks for any help with this!

 

[venkat1926]

David McRitchie has given a function for obtaining the text of the formula in a cell.
http://dmcritchie.mvps.org/excel/formula.htm

now copy this function and the next macro in a module and run the macro test

Function GetFormulaI(Cell As Range) As String
'given DAVID McRITCHIE
'Application.Volatile = True
   If VarType(Cell) = 8 And Not Cell.HasFormula Then
    GetFormulaI = "'" & Cell.Formula
   Else
    GetFormulaI = Cell.Formula
   End If
   If Cell.HasArray Then _
     GetFormulaI = "{" & Cell.Formula & "}"
End Function

Sub test()
Dim j As Integer, x As String, k As Integer
x = GetFormulaI(Range("A1"))
j = 0
k = 0
Do
'MsgBox j
'MsgBox k
On Error GoTo jjj
k = WorksheetFunction.Search("$F$7", x, j + 1 + k)
If IsNumeric(k) Then
j = j + 1
Else
Exit Do
End If
Loop
jjj:
MsgBox j
End Sub

 

[venkat1926]

there is a mistake in the macro "test". replace the macro test by this

Sub test()
Dim j As Integer, x As String, k As Integer, y As String
x = GetFormulaI(Range("A1"))
y = "$F$7"
j = 0
k = 0
Do
'MsgBox j
'MsgBox k
On Error GoTo jjj
k = WorksheetFunction.Search(y, x, k + 1)
If IsNumeric(k) Then
j = j + 1
k = k + Len(y)
Else
Exit Do
End If
Loop
jjj:
MsgBox j
End Sub

 

[Ralajer]

I don't believe there is a non-VBA solution to your problem other than using your eyes. Here is another VBA solution. Add it to the VBA module and in B1 =inFormula(A1,$F$1) and copy it down to B10. The first argument is the cell it is searching in and the second is what cell reference to check for.

Function inFormula(searchCell As Range, refCell As Range) As Integer
    Dim search As String
    Dim ref As String
    Dim i As Integer

    search = UCase(Replace(searchCell.Formula, "$", ""))
    ref = Replace(refCell.Address, "$", "")
    
    For i = 1 To Len(search)
        If Mid(search, i, Len(ref)) = ref And _
                Not IsNumeric(Mid(search, i + Len(ref), 1)) Then _
                inFormula = inFormula + 1
    Next i
End Function

It will find any type of reference to F1 be it absolute or relative. It won't find F1 if it is within a range (D1:G1). The return value will be the count.