total a column in which there are sum formula

  • Thread starter Thread starter VKL Narayanan
  • Start date Start date
V

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?
 
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
 
should be:

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

sorry
 
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
 
VKL said:
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?
Click to expand...


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