Excel Formula/VBA code that will sum only cells containing values only (skip any cells containing formulas)?

Question

Is there a formula that will sum ONLY cells that contain a value (not cells with a formula)? For example, say in column A of a spreadsheet I have a mixture of entered values and formulas that return values. If I use a sum formula at the end it will naturally sum all of the numbers in the selected array regardless of whether they are entered values or values resulting from a formula. (Maybe some kind of SUMIF & VBA code combo..) In case my description wasn't clear, here is a hypothetical spreadsheet set-up where i would need this formula:

      A
1|  400
2|  =SUM(B1:B3)
3|  =AVERAGE(B1:B3)
4|  200
5|  100
6|  =COUNT(B1:B3)
7|  I want the sum Formula in this cell (A7) to return the value 700 (the sum of the values above).

Answer 1

To clarify Martin's answer.

There is no way to know if a cell contains a formula using Excel formulas.

You have to define an UDF (user defined function). Tutorial here. . From the tutorial:

1. Open up a new workbook.
2. Get into VBA (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste the Excel user defined function examples
5. Get out of VBA (Press Alt+Q)
6. Use the functions (They will appear in the Paste Function dialog box, Shift+F3, under the "User Defined" category)

Your UDF will look something like:

Public Function isformula(rng As Range) As Variant()
    Dim aryIn() As Variant
    Dim a As Variant
    Dim i As Integer, j As Integer
    Dim temp() As Variant

    aryIn = rng.Value
    ReDim temp(LBound(aryIn) To UBound(aryIn), _
               LBound(aryIn, 2) To UBound(aryIn, 2))
    For i = LBound(aryIn) To UBound(aryIn)
        For j = LBound(aryIn, 2) To UBound(aryIn, 2)
            If (Left(rng(i, j).Formula, 1) = "=") Then
               temp(i, j) = True
            Else
               temp(i, j) = False
            End If
        Next j
    Next i
    isformula = temp()
End Function

Then you may use it in your code. Something like:

{=SUM(IF(NOT(isformula(A1:A6)),A1:A6,0))}

Where the braces {} indicate an ARRAY formula (entered by Ctrl-Shift-Enter)

HTH!

Answer 2

If you use SUBTOTAL for all of your functions, you can do it. SUBTOTAL will ignore any other SUBTOTAL functions in the range. In A2

=SUBTOTAL(9,B1:B3)

In A3

=SUBTOTAL(1,B1:B3)

In A6

=SUBTOTAL(2,B1:B3)

In A7

=SUBTOTAL(9,A1:A6)

A7 will be 700 (which is what I assume you meant). If you have formulas that aren't an option in SUBTOTAL, then it won't work.

Answer 3

There is a HasFormula Property which you may be able to combine with SUMIF to do what you want.

Answer 4

This will work, though somehow it feels sloppy and there must surely be a better way. With a little extra work you could make this into a UDF.

Sub SumNumbersOnly()
    Dim sumAllCells As Long
    Dim sumFormulaCells As Long
    Dim sumNumberCells As Long

    sumAllCells = Application.Sum(Selection)
    sumFormulaCells = Application.Sum(Selection.Cells.SpecialCells(xlCellTypeFormulas))

    sumNumberCells = sumAllCells - sumFormulaCells

    Debug.Print sumNumberCells //Returns 700 (400 + 200 + 100 as in your example)

End Sub