Text string and sum
 

Good Afternoon

I have 2 columns one with a string of numbers and the other a number

1,-,-,-,-,-,-,-,-,- 247
-,2,-,-,-,-,-,-,-,- 34
-,-,3,-,-,-,-,-,-,- 34
-,-,-,4,-,-,-,-,-,- 24
-,-,-,-,5,-,-,-,-,- 2608
-,-,-,-,-,6,-,-,-,- 2


What I want to do is search the range of text string to find lets say the number 2 (the numbers appear in the same position every time or is replaces with the - ) then sum the next column that relates to the number 2 etc

I have 1023 lines of text string (total number of combinations of 10)



Thank you

Text string and sum
 

Hi!

One way:

Enter this formula in say, E1:

=ROWS($1:1)-1

Enter this formula in F1:

=SUMIF(A$1:A$6,"*"&E1&"*",B$1:B$6)

Select both E1 and F1 and copy down to row 10.

Biff

"michaelp" wrote in message
...

Good Afternoon

I have 2 columns one with a string of numbers and the other a number

1,-,-,-,-,-,-,-,-,- 247
-,2,-,-,-,-,-,-,-,- 34
-,-,3,-,-,-,-,-,-,- 34
-,-,-,4,-,-,-,-,-,- 24
-,-,-,-,5,-,-,-,-,- 2608
-,-,-,-,-,6,-,-,-,- 2


What I want to do is search the range of text string to find lets say
the number 2 (the numbers appear in the same position every time or is
replaces with the - ) then sum the next column that relates to the
number 2 etc

I have 1023 lines of text string (total number of combinations of 10)



Thank you


--
michaelp

Text string and sum
 

hi, michaelp !

... 2 columns one with a string of numbers and the other a number
1,-,-,-,-,-,-,-,-,- 247
-,2,-,-,-,-,-,-,-,- 34
-,-,3,-,-,-,-,-,-,- 34
-,-,-,4,-,-,-,-,-,- 24
-,-,-,-,5,-,-,-,-,- 2608
-,-,-,-,-,6,-,-,-,- 2
... search the range of text string to find lets say the number 2
(the numbers appear in the same position every time or is replaced with the - )
then sum the next column that relates to the number 2 etc [...]

I'm not so sure if comma is part of the string, and if you want to 'sum'... or 'add' to the next column :D
you may want to try with...
op1: =substitute(substitute(a1,"-",""),",","")+b1
op2: =--(substitute(substitute(a1,"-",""),",","")&b1)

hth,
hector.

Text string and sum
 

On Mon, 14 Nov 2005 02:37:09 +0000, michaelp
wrote:


Good Afternoon

I have 2 columns one with a string of numbers and the other a number

1,-,-,-,-,-,-,-,-,- 247
-,2,-,-,-,-,-,-,-,- 34
-,-,3,-,-,-,-,-,-,- 34
-,-,-,4,-,-,-,-,-,- 24
-,-,-,-,5,-,-,-,-,- 2608
-,-,-,-,-,6,-,-,-,- 2


What I want to do is search the range of text string to find lets say
the number 2 (the numbers appear in the same position every time or is
replaces with the - ) then sum the next column that relates to the
number 2 etc

I have 1023 lines of text string (total number of combinations of 10)



Thank you

Perhaps something like:

=SUMIF(A1:A1023,"*2*",B1:B1023)


--ron

Thank you all for taking the time to look at this for me.