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

"Rick Rothstein" wrote:

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 (MVP - Excel)


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