sum if cell contains a formula

[jimtodd]

How cant I add a collum of data but only cells with a formula in.

[Bob Phillips]

=SUMPRODUCT(--IsFormula(A1:A100))

and add this UDF

'---------------------------------------------------------------------
Function IsFormula(rng As Range) As Variant
'---------------------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim aryFormulae As Variant

If rng.Areas.Count > 1 Then
aryFormulae = CVErr(xlErrValue)
Exit Function
End If

If rng.Cells.Count = 1 Then
aryFormulae = rng

Else
aryFormulae = rng.Value
i = 0

For Each row In rng.Rows
i = i + 1
j = 0

For Each cell In row.Cells
j = j + 1

aryFormulae(i, j) = cell.HasFormula

Next cell

Next row

End If

IsFormula = aryFormulae

End Function



--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"jimtodd" <[email protected]> wrote in message
news:[email protected]...
> How cant I add a collum of data but only cells with a formula in.