total a column in which there are sum formula
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? |
total a column in which there are sum formula
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 |
total a column in which there are sum formula
should be:
this function will sum all cells in a range that DO NOT include ANY formulae sorry |
total a column in which there are sum formula
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 |
total a column in which there are sum formula
VKL Narayanan wrote:
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. |
All times are GMT +1. The time now is 12:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com