Help with Sum formula

Nasp

New member
Joined
Oct 22, 2011
Messages
1
Reaction score
0
Points
0

icon1.gif
Help with sum formula

Hello,

I am new to excel and and it would be amazing if anyone could help me out.

I have around 2000 rows with a set of 8 different numbers in each row. For instance,

2-4-7-11-15-29-35-36
4-16-20-29-32-37-49-50
etc...

Is it possible to be able to get if any 2 numbers in each line add up to a 3rd number?

For instance, in

1) 4-7-11-12-40-42-56-83, 4 + 7 add up to 11.

So I would like to get the sum 11.

2) If there are more two pairs of numbers that add up to a third number, for instance, in 1-2-4-16-20-29-49-210, 4 + 16 add up to 20 and 20+29 add up to 49, I would like to get again as a result the two sums.

If the above is not possible, please suggest any other way that I can get an indication that the lines have the above characteristic. For instance instead of getting the sums, get the number 1 instead of 11 as in the first example and get the number 2 when two pairs add up to a sum as in the second example.

I hope someone can help me out.

Thank you

Nasp
 

vinay

New member
Joined
Oct 23, 2011
Messages
10
Reaction score
0
Points
0
Whether the data contain in a row i.e. 2-4-7-11-15-29-35-36 is in different column or a single column?
 

RichDenman

New member
Joined
Dec 14, 2011
Messages
7
Reaction score
0
Points
0
Excel Version(s)
2016
Yes this can be done. There are 2 ways that I can think of to do it but if you are new to excel it may be tricky. First you have to identify all number combinations that can give you your total... So if you are looking at numbers that total 8 you would have 1+7, 2+6, 3+5 and 4+4. you can then use a combination of if and find statments to identify if it is present. Post some source data and I will have a look
 
Last edited:

Jesse

New member
Joined
Mar 22, 2011
Messages
51
Reaction score
0
Points
0
The below works for me as a user defined function. Since you could return multiple results you can pass an optional argument that specifies which number of iteration it returns. Ie you would call it as =findSums(A1,3) where 3 is the third match.

The code:

Code:
Function findSums(s As String, n As Integer) As String


Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim numberCount As Integer
Dim numberInstance As Integer
Dim numberFactors As Variant
Dim tempSum As Integer


numberInstance = 0
numberFactors = Split(s, "-")
numberCount = UBound(numberFactors)


For i = 0 To numberCount
    For j = i To numberCount
        For k = 0 To numberCount
            If i = j Then
            ElseIf CInt(numberFactors(k)) = CInt(numberFactors(i)) + CInt(numberFactors(j)) Then
                numberInstance = numberInstance + 1
                If numberInstance = n Then
                    findSums = numberFactors(i) & " & " & numberFactors(j) & " add to equal " & numberFactors(k)
                End If
            End If
        Next k
    Next j
Next i


End Function
 
Top