Sum the first 10 numbers in a 26 cell range

[jester]

I have to sum the first 10 numbers in a 26 cell range. The problem is
that I do not know which cells the numbers are in. The 26 cells
contain single quotes (') to begin with. The numbers are typed in as
the event occurs. I do not know which cells they will be typed in. I
only want the first 10 numbers to be sumed. This can be a formula or a
macro. The formula is preferred. Thanks in advance.

 

[John]

how about, assuming your 26 cells start in ell a5

put this in b5 and copy down
=IF(ISERROR(VALUE(A5))=TRUE,0,VALUE(A5))

put this in c5 and copy down
=COUNTIF(B5,">0")

put this in c5 and copy down
=IF(SUM($C$5:C5)<10,SUM($B$5:B5),D4)

your sum will be the max of column d

John

 

[Debra Dalgleish]

Assuming the 26 cells are A2:A27, enter the following formula in cell B2:

=IF(COUNT($A$1:A2)>10,"",N(B1)+N(A2))

anc copy it down to B27

 

[Don]

jester,

=SUM(OFFSET($A$2,0,0,SMALL(IF(ISBLANK(A2:A27),"",ROW(A2:A27)),G1)-1,1))
(G1 is = 10)
enter as array formula

 

[Don]

Forgot they were text

try:

Function addtext(myrange, howmany)
For Each r In myrange
If r <> "" Then
counter = counter + 1
total = total + Val(r)
addtext = total
If counter = 10 Then Exit Function
Else
End If
Next
End Function

Don

 

[Don]

Sorry, Try again

Function addtext(myrange, howmany)
For Each r In myrange
If r <> "" Then
counter = counter + 1
total = total + Val(r)
addtext = total
If counter = howmany Then Exit Function
Else
End If
Next
End Function

Don

 

[Leo Heuser]

A single-cell formula

=SUM(OFFSET($A$2,0,0,SMALL(IF(A2:A27<>"",ROW(A2:A27)),10)-1,1))

This is an array formula, and it must be entered with
<Shift><Ctrl><Enter> instead of <Enter>, also if
edited later.