View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default sum if cell contains a formula

=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" wrote in message
...
How cant I add a collum of data but only cells with a formula in.