Distinguishing between Cells with Formula vs Cells with Text/Value

B

bstobart

I want to be able to distinguish cells having formulas from cells having just
values. In my particular case, the values will all be numeric. Is there an
easy way to do that?

My goal is to sum all the values in a given column, ignoring any cells
containing formulas that may also be in the column.

I'd like to avoid using a macro or User defined function.
 
M

muddan madhu

select the column | Ctrl + G | click special | select constants | ok

on stutus bar right click | sum |
 
M

muddan madhu

Try this UDF ( user defined function)

Function Sumnum(rng As Range)
Dim r As Range
Dim v
For Each r In rng
If r.HasFormula = False Then
v = WorksheetFunction.Sum(r) + v
End If
Next r
Sumnum = v
End Function
 
Top