total a column in which there are sum formula

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?
 
J

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
 
J

Jarek Kujawa

should be:

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

sorry
 
J

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
 
G

Glenn

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?


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top