Is it possible, given a row of mixed values & formulas, to sum just the
numbers that are values, not the numbers that are generated by formulas?
(Excel 2003). If Sumif can't handle that, is there another way of doing so?
Why do you want a formula that sums only constants? What exclude
formulas that return numbers?
Seems like an odd requirement. I'm just wondering if something else
would satisfy your ultimate needs. For example, perhaps you could
simply use Edit > Goto (ctrl-G) > Special, select Constants and Number
(deselect everything else), and look at Sum on the status bar.
Alternatively, you could enter the UDF myHasFormula below and use it
as:
=SUMPRODUCT(--(myHasFormula(A1:A100)=FALSE),A1:A100)
The myHasFormula function can serve many purposes. But of course, you
could simply use the UDF sumConstants below as follows:
=sumConstants(A1:A100)
The UDFs....
Function myHasFormula(rng As Range)
Dim r As Long, c As Long, i As Long, j As Long
If TypeName(rng) = "Range" Then
If rng.Count = 1 Then
myHasFormula = rng.HasFormula
Else
r = rng.Rows.Count
c = rng.Columns.Count
ReDim x(1 To r, 1 To c) As Boolean
For i = 1 To r: For j = 1 To c
x(i, j) = rng.Cells(i, j).HasFormula
Next: Next
myHasFormula = x
End If
Else
myHasFormula = False
End If
End Function
Function sumConstants(rng As Range) As Double
Dim r As Long, c As Long, i As Long, j As Long, s As Double
If TypeName(rng) = "Range" Then
r = rng.Rows.Count
c = rng.Columns.Count
s = 0
For i = 1 To r: For j = 1 To c
If Not rng.Cells(i, j).HasFormula Then
' use IsNumeric(...) instead of
WorksheetFunction.IsNumber(...)
' if you want to include text numbers -- numbers entered
' or formatted as text
If WorksheetFunction.IsNumber(rng.Cells(i, j)) Then s = s +
rng.Cells(i, j)
End If
Next: Next
sumConstants = s
End If
End Function
