Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for average of a top 12 numbers in a column of 24 total nu | Excel Discussion (Misc queries) | |||
FORMULA; ADD A COLUMN, SUBTRACT 1 CELL, TAKE THE TOTAL X 10% | Excel Discussion (Misc queries) | |||
Formula to add top 4 entries in a column and total them at the bot | Excel Worksheet Functions | |||
XL formula - total of row = total of column | Excel Worksheet Functions | |||
Create an if-then formula in Excel to limit column total? | Excel Discussion (Misc queries) |