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.

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
-----Original Message-----
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.
.

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

jester wrote:
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.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

jester,

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

"jester" wrote in message
om...
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.

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" wrote in message
...
jester,

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

"jester" wrote in message
om...
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.

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
"Don" wrote in message
...
jester,

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

"jester" wrote in message
om...
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.

A single-cell formula

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

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


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"jester" skrev i en meddelelse
om...
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.