Count Formulas

[excel_novice01]

Is there a way that I can count the number of cells that have a formula?

Cell 1: 5
Cell 2: 8
Cell 3: Sum(a1:a2)
Cell 4: 2
Cell 5: 9
Cell 6: Sum(a4:A5)


I would like a formula that would return 2.

 

[ZAX]

Is there a way that I can count the number of cells that have a formula?

Cell 1: 5
Cell 2: 8
Cell 3: Sum(a1:a2)
Cell 4: 2
Cell 5: 9
Cell 6: Sum(a4:A5)


I would like a formula that would return 2.

Hi,
Use this:

Function CountFormulas(Rng As Range)
Dim Cell As Range
For Each Cell In Rng
    If Cell.HasFormula Then
       i = i + 1
    End If
Next
CountFormulas = i
End Function

ZAX

 

[XLfriend777]

Hi excel_novice01 - Try running the code below and adjusting the Range to suit your needs. You can also use the Find & Select -> GoTo -> Special -> Formulas from the Ribbon and only the cells with formulas will be selected. At the bottom of your sheet the number selected should appear.

Hope this helps.

Sub CountFormulas()
Dim Count As Long
    Range("A1:D15").Select
    Selection.SpecialCells(xlCellTypeFormulas, 23).Select
    
    Count = Selection.Count
    MsgBox (Count)
    
End Sub

 

[Rick Rothstein]

Is there a way that I can count the number of cells that have a formula?

Cell 1: 5
Cell 2: 8
Cell 3: Sum(a1:a2)
Cell 4: 2
Cell 5: 9
Cell 6: Sum(a4:A5)

I would like a formula that would return 2.

Are your "formulas" in cells 3 and 6 real Excel formulas (that is, do you see a leading equal sign in the Formula Bar for those cells) or are they as shown, text, without a leading equal sign (when looked at in the Formula Bar)?