sum of text positions

[Kevin]

Range A1= 15,10,1,8,1,9,0,1
Range A2= 15,10,1,8,1,9,0,1

I need 8 different formulas for A3-A10 to get the sum of each position.
So A3 would only get the sum of the first number in A1 and A2 (30)
A4 would get the sum of the second number in A1 and A2 (20)
A5=2
A6=16
A7=2
and so on...

Is that do-able?

Thanks,
Kevin

 

[Michael]

The Numbers you have is a string isn't it?
You could try something like this:
=SUM(VALUE(MID(A1,1,2)),VALUE(MID(A1,1,2)))
Granted in my example the number of digits is always 2; how often will the
numbers change? you can substitute the Last Parameter for a Find, searching
for a comma.

 

[Michael]

The sercond value should read A2 not A!, then you will have to adjust it for
the next set of values.
=SUM(VALUE(MID(A1,1,2)),VALUE(MID(A2,1,2)))
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

 

[Ron Rosenfeld]

Range A1= 15,10,1,8,1,9,0,1
Range A2= 15,10,1,8,1,9,0,1

I need 8 different formulas for A3-A10 to get the sum of each position.
So A3 would only get the sum of the first number in A1 and A2 (30)
A4 would get the sum of the second number in A1 and A2 (20)
A5=2
A6=16
A7=2
and so on...

Is that do-able?

Thanks,
Kevin

There are a number of ways to do this.

One way is to use a UDF which interprets "Regular Expression".

To enter the UDF, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this UDF, enter the following formula into A3 and fill down to A10.

If there are not "matching" entries in A1 and A2, the formula will return a
#VALUE! error.

A3: =RegexMid($A$1,"\d+",ROWS($1:1))+RegexMid($A$2,"\d+",ROWS($1:1))

============================================
Option Explicit
Function RegexMid(Str As String, Pattern As String, _
Optional Index As Variant = 1, _
Optional CaseSensitive As Boolean = True, _
Optional MultiLin As Boolean = False) _
As Variant 'Variant as value may be string or array


Dim objRegExp As Object
Dim objMatch As Object
Dim colMatches As Object

Dim i As Long 'counter
Dim T() As String 'container for array results

' Create a regular expression object.
Set objRegExp = CreateObject("vbscript.regexp")

'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern

' Set Case Insensitivity.
objRegExp.IgnoreCase = Not CaseSensitive

'Set global applicability.
objRegExp.Global = True

'Set multiline
objRegExp.MultiLine = MultiLin

'Test whether the String can be compared.
If (objRegExp.Test(Str) = True) Then

'Get the matches.
Set colMatches = objRegExp.Execute(Str) ' Execute search.

On Error Resume Next 'return null string if a colmatch index is non-existent
If IsArray(Index) Then
ReDim T(1 To UBound(Index))
For i = 1 To UBound(Index)
T(i) = colMatches(Index(i) - 1)
Next i
RegexMid = T()
Else
RegexMid = CStr(colMatches(Index - 1))
If IsEmpty(RegexMid) Then RegexMid = ""
End If
On Error GoTo 0 'reset error handler
Else
RegexMid = ""
End If
End Function
=======================================
--ron

 

[Kevin]

The digits could change at any time> I was looking at find but I'm not sure
how to get it to find anything but the first instance.

 

[Ron Rosenfeld]

There are a number of ways to do this.

One way is to use a UDF which interprets "Regular Expression".

To enter the UDF, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this UDF, enter the following formula into A3 and fill down to A10.

If there are not "matching" entries in A1 and A2, the formula will return a
#VALUE! error.

A3: =RegexMid($A$1,"\d+",ROWS($1:1))+RegexMid($A$2,"\d+",ROWS($1:1))

============================================
Option Explicit
Function RegexMid(Str As String, Pattern As String, _
Optional Index As Variant = 1, _
Optional CaseSensitive As Boolean = True, _
Optional MultiLin As Boolean = False) _
As Variant 'Variant as value may be string or array


Dim objRegExp As Object
Dim objMatch As Object
Dim colMatches As Object

Dim i As Long 'counter
Dim T() As String 'container for array results

' Create a regular expression object.
Set objRegExp = CreateObject("vbscript.regexp")

'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern

' Set Case Insensitivity.
objRegExp.IgnoreCase = Not CaseSensitive

'Set global applicability.
objRegExp.Global = True

'Set multiline
objRegExp.MultiLine = MultiLin

'Test whether the String can be compared.
If (objRegExp.Test(Str) = True) Then

'Get the matches.
Set colMatches = objRegExp.Execute(Str) ' Execute search.

On Error Resume Next 'return null string if a colmatch index is non-existent
If IsArray(Index) Then
ReDim T(1 To UBound(Index))
For i = 1 To UBound(Index)
T(i) = colMatches(Index(i) - 1)
Next i
RegexMid = T()
Else
RegexMid = CStr(colMatches(Index - 1))
If IsEmpty(RegexMid) Then RegexMid = ""
End If
On Error GoTo 0 'reset error handler
Else
RegexMid = ""
End If
End Function
=======================================
--ron

Another consideration:

If your values are not all integers; in other words, if some of them are
include decimals, then you may want to use this formula instead, to split the
string up at the commas:

=regexmid($A$1,"[^,]+",ROWS($1:1))+regexmid($A$2,"[^,]+",ROWS($1:1))

--ron

 

[Kevin]

Well, that works and I like it, however when I stated my example I simplified
it thinking I could change the formula prett easy. In actuallity the cells
that need to be added are
C4, E4, G4, I4, K4, M4, O4, Q4, S4, U4, W4, Y4, AA4, AC4, AE4, AG4

That being the case how would the RegexMid formula be written?

There are a number of ways to do this.

One way is to use a UDF which interprets "Regular Expression".

To enter the UDF, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this UDF, enter the following formula into A3 and fill down to A10.

If there are not "matching" entries in A1 and A2, the formula will return a
#VALUE! error.

A3: =RegexMid($A$1,"\d+",ROWS($1:1))+RegexMid($A$2,"\d+",ROWS($1:1))

============================================
Option Explicit
Function RegexMid(Str As String, Pattern As String, _
Optional Index As Variant = 1, _
Optional CaseSensitive As Boolean = True, _
Optional MultiLin As Boolean = False) _
As Variant 'Variant as value may be string or array


Dim objRegExp As Object
Dim objMatch As Object
Dim colMatches As Object

Dim i As Long 'counter
Dim T() As String 'container for array results

' Create a regular expression object.
Set objRegExp = CreateObject("vbscript.regexp")

'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern

' Set Case Insensitivity.
objRegExp.IgnoreCase = Not CaseSensitive

'Set global applicability.
objRegExp.Global = True

'Set multiline
objRegExp.MultiLine = MultiLin

'Test whether the String can be compared.
If (objRegExp.Test(Str) = True) Then

'Get the matches.
Set colMatches = objRegExp.Execute(Str) ' Execute search.

On Error Resume Next 'return null string if a colmatch index is non-existent
If IsArray(Index) Then
ReDim T(1 To UBound(Index))
For i = 1 To UBound(Index)
T(i) = colMatches(Index(i) - 1)
Next i
RegexMid = T()
Else
RegexMid = CStr(colMatches(Index - 1))
If IsEmpty(RegexMid) Then RegexMid = ""
End If
On Error GoTo 0 'reset error handler
Else
RegexMid = ""
End If
End Function
=======================================
--ron

Another consideration:

If your values are not all integers; in other words, if some of them are
include decimals, then you may want to use this formula instead, to split the
string up at the commas:

=regexmid($A$1,"[^,]+",ROWS($1:1))+regexmid($A$2,"[^,]+",ROWS($1:1))

--ron

 

[Rick Rothstein]

Here is my take on a UDF solution (see notes after the code)...

Function AddCellNums(IndexNum As Long, ParamArray CellRef()) As Variant
Dim C As Variant
Dim CC As Range
If UBound(CellRef) = -1 Then
AddCellNums -CVErr(xlErrValue)
Exit Function
ElseIf Not TypeOf CellRef(LBound(CellRef)) Is Range Then
AddCellNums -CVErr(xlErrRef)
Exit Function
Else
On Error GoTo CancelFunction
For Each C In CellRef
If C.Count = 1 Then
AddCellNums = AddCellNums + CDbl(Split(C, ",")(IndexNum - 1))
Else
For Each CC In C
AddCellNums = AddCellNums + CDbl(Split(CC, ",")(IndexNum - 1))
Next
End If
Next
End If
Exit Function
CancelFunction:
AddCellNums = CVErr(xlErrNum)
End Function

This function can handle more than two cell... just put the range or cell
references in a comma delimited list after the item number in the list that
you want to find. So, if you wanted the sum of the 3 item in cells A1, A2
and A4, you would call the UDF with this formula...

=AddCellNums(3,A1:A2,A4)

However, since you will want to copy this formula down in order to get all
the individual sums, you will need to make the index number variable and the
cell references absolute. So, to get all the sum, use something like this...

=AddCellNums(ROW(A1),A$1:A$2,A$4)

and copy that down.

Note that if all the cells do not contain a list of values containing the
same number of values in each list, an error is generated.

Oh, and if you do not know how to install a UDF, follow this procedure.
Press Alt+F11 to get into the VB editor and then click Insert/Module from
its menu bar, then just copy/paste the above function into the code window
that opened up. That's it... you can now use the AddCellNums like any other
worksheet function (provided you feed it the correct arguments as outlined
above).

 

[Rick Rothstein]

While waiting for Ron to modify his function, you might want to try out the
function I just posted. You would use this formula to call it...

=AddCellNums(ROW(A1),C4,E4,G4,I4,K4,M4,O4,Q4,S4,U4,W4,Y4,AA4,AC4,AE4,AG4)

and then copy it down as needed.

--
Rick (MVP - Excel)

Well, that works and I like it, however when I stated my example I
simplified
it thinking I could change the formula prett easy. In actuallity the
cells
that need to be added are
C4, E4, G4, I4, K4, M4, O4, Q4, S4, U4, W4, Y4, AA4, AC4, AE4, AG4

That being the case how would the RegexMid formula be written?

On Thu, 11 Dec 2008 09:17:01 -0800, Kevin
<[email protected]>
wrote:

Range A1= 15,10,1,8,1,9,0,1
Range A2= 15,10,1,8,1,9,0,1

I need 8 different formulas for A3-A10 to get the sum of each position.
So A3 would only get the sum of the first number in A1 and A2 (30)
A4 would get the sum of the second number in A1 and A2 (20)
A5=2
A6=16
A7=2
and so on...

Is that do-able?

Thanks,
Kevin

There are a number of ways to do this.

One way is to use a UDF which interprets "Regular Expression".

To enter the UDF, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste
the
code below into the window that opens.

To use this UDF, enter the following formula into A3 and fill down to
A10.

If there are not "matching" entries in A1 and A2, the formula will
return a
#VALUE! error.

A3: =RegexMid($A$1,"\d+",ROWS($1:1))+RegexMid($A$2,"\d+",ROWS($1:1))

============================================
Option Explicit
Function RegexMid(Str As String, Pattern As String, _
Optional Index As Variant = 1, _
Optional CaseSensitive As Boolean = True, _
Optional MultiLin As Boolean = False) _
As Variant 'Variant as value may be string or array


Dim objRegExp As Object
Dim objMatch As Object
Dim colMatches As Object

Dim i As Long 'counter
Dim T() As String 'container for array results

' Create a regular expression object.
Set objRegExp = CreateObject("vbscript.regexp")

'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern

' Set Case Insensitivity.
objRegExp.IgnoreCase = Not CaseSensitive

'Set global applicability.
objRegExp.Global = True

'Set multiline
objRegExp.MultiLine = MultiLin

'Test whether the String can be compared.
If (objRegExp.Test(Str) = True) Then

'Get the matches.
Set colMatches = objRegExp.Execute(Str) ' Execute search.

On Error Resume Next 'return null string if a colmatch index is
non-existent
If IsArray(Index) Then
ReDim T(1 To UBound(Index))
For i = 1 To UBound(Index)
T(i) = colMatches(Index(i) - 1)
Next i
RegexMid = T()
Else
RegexMid = CStr(colMatches(Index - 1))
If IsEmpty(RegexMid) Then RegexMid = ""
End If
On Error GoTo 0 'reset error handler
Else
RegexMid = ""
End If
End Function
=======================================
--ron

Another consideration:

If your values are not all integers; in other words, if some of them are
include decimals, then you may want to use this formula instead, to split
the
string up at the commas:

=regexmid($A$1,"[^,]+",ROWS($1:1))+regexmid($A$2,"[^,]+",ROWS($1:1))

--ron

 

[Kevin]

Ok, this i can wrap my brain around. This works great for me. I dont need to
drag it down nessecarily these formulas will be on a template that gets
copied in so the formulas will already be there.

Thanks alot guys for your help. I learned some new stuff!!

Kevin

 

[Ron Rosenfeld]

Well, that works and I like it, however when I stated my example I simplified
it thinking I could change the formula prett easy. In actuallity the cells
that need to be added are
C4, E4, G4, I4, K4, M4, O4, Q4, S4, U4, W4, Y4, AA4, AC4, AE4, AG4

One way:

=SUM(RegexMid($C$4,"[^,]+",ROWS($1:1)),RegexMid($E$4,"[^,]+",ROWS($1:1))),...)

But doing it all within a UDF would probably be simpler.
--ron