Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
Thank you all for taking the time to look at this for me.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|