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

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

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

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

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