Summing numbers only (not formulas)

[DaveTJDaniels]

Hi,

I am trying to sum only hardcoded numbers in a column, and ignore any formulas.

I was hoping that the AGGREGATE function would do the job, but it only ignores subtotals.

Any help you can give would be much appreciated.

many thanks,
David

 

[lrobbo314]

How about this?

MXL 20211228.xlsm
	T	U	V
8	1		6
9	2
10	3
11	7
Sheet6

Cell Formulas
Range	Formula
V8	V8	=sumh(T8:T11)
T11	T11	=T10*T9+T8

Function SUMH(r As Range)
Dim c As Range

For Each c In r
    If Not c.HasFormula Then SUMH = SUMH + c.Value
Next c
End Function

 

[bstory84]

I like to use Sumproduct with IsFormula.
replace A2:A6 with your range.

=SUMPRODUCT(--(ISFORMULA(A2:A6)=FALSE),(A2:A6))

 

[RoryA]

Just FYI, if the OP is actually using 2010 per his profile, ISFORMULA is not available.

 

[Fluff]

@bstory84 isformula only appeared in xl2013, so the OP doesn't have it.

 

[DaveTJDaniels]

Perfect! Many thanks for your help!