total a column in which there are sum formula

[VKL Narayanan]

I have a column in which I have given for sub total the sum formula that
=sum(F6..F8), like this there are several of them in that column.

However at the bottom of the column I want the total of the column excluding
the cells that have the formula =sum

How can I do that?

 

[Jarek Kujawa]

to me you have to define your own function

Function sum_no_formulas(target As Range) As Double

For Each cell In target
If Not cell.HasFormula Then
sum_no_formulas = sum_no_formulas + cell.Value
End If
Next cell

End Function

this function will sum all cells in a range that include ANY formulae
(i.e. not only those with SUM function but ANY cells strating with =
sign)

check this one out and let me know if it works for you

 

[Jarek Kujawa]

should be:

this function will sum all cells in a range that DO NOT include ANY
formulae

sorry

 

[Jarek Kujawa]

another option, sums only cells with NO SUM function in them (but WILL
sum cell with formulae other than SUM)

Function sum_no_formulas(target As Range) As Double

For Each cell In target
If Not cell.Formula Like "*=SUM*" Then
sum_no_formulas = sum_no_formulas + cell.Value
End If
Next cell

End Function

HIH

 

[Glenn]

I have a column in which I have given for sub total the sum formula that
=sum(F6..F8), like this there are several of them in that column.

However at the bottom of the column I want the total of the column excluding
the cells that have the formula =sum

How can I do that?

If possible, use the SUBTOTAL function instead of SUM for your sub totals in the
column and for the column total at the bottom.