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

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).

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.

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!

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

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