Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing only numbers, not formulas
How do I write a SUMIF fcn that will only sum the cells of a column that
contain only numbers. I do not want to include numbers that are calculated by a formula. I appreciate your help, -John |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing only numbers, not formulas
Can be done with VBA function
Function mysum(myrange) For Each mycell In myrange If Not mycell.HasFormula Then mysum = mysum + mycell End If Next End Function Called from worksheet with =mysum(H1:H100) , for example New to VBA? See David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "John" wrote in message ... How do I write a SUMIF fcn that will only sum the cells of a column that contain only numbers. I do not want to include numbers that are calculated by a formula. I appreciate your help, -John |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing only numbers, not formulas
First install this tiny UDF:
Function noformula(r As Range) As Boolean noformula = Not r.hasformula End Function Then, if your data is in column A,from A1 thru A100, in B1 enter: =noformula(A1) and copy down Finally enter: =SUMPRODUCT((A1:A100)*(B1:B100)) -- Gary''s Student - gsnu200832 "John" wrote: How do I write a SUMIF fcn that will only sum the cells of a column that contain only numbers. I do not want to include numbers that are calculated by a formula. I appreciate your help, -John |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing only numbers, not formulas
I had to change the if stmt to
If IsNumeric(mycell) And Not mycell.HasFormula Then in order not to get a #VALUE! error if cell had text. This works fine now. Thx so much, John "Bernard Liengme" wrote: Can be done with VBA function Function mysum(myrange) For Each mycell In myrange If Not mycell.HasFormula Then mysum = mysum + mycell End If Next End Function Called from worksheet with =mysum(H1:H100) , for example New to VBA? See David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "John" wrote in message ... How do I write a SUMIF fcn that will only sum the cells of a column that contain only numbers. I do not want to include numbers that are calculated by a formula. I appreciate your help, -John |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing only numbers, not formulas
I did not think about cells having text, sorry
Glad you solved it all the best -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "John" wrote in message ... I had to change the if stmt to If IsNumeric(mycell) And Not mycell.HasFormula Then in order not to get a #VALUE! error if cell had text. This works fine now. Thx so much, John "Bernard Liengme" wrote: Can be done with VBA function Function mysum(myrange) For Each mycell In myrange If Not mycell.HasFormula Then mysum = mysum + mycell End If Next End Function Called from worksheet with =mysum(H1:H100) , for example New to VBA? See David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "John" wrote in message ... How do I write a SUMIF fcn that will only sum the cells of a column that contain only numbers. I do not want to include numbers that are calculated by a formula. I appreciate your help, -John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing a group of numbers | New Users to Excel | |||
Summing numbers | Excel Discussion (Misc queries) | |||
Keep getting #### when summing numbers | Excel Discussion (Misc queries) | |||
summing more than 30 numbers | Excel Discussion (Misc queries) | |||
Summing Numbers | Excel Discussion (Misc queries) |