Summing only numbers, not formulas

[John]

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

 

[Bernard Liengme]

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

 

[Gary''s Student]

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))

 

[John]

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]

I did not think about cells having text, sorry
Glad you solved it
all the best