View Full Version : Help with Sum formula

2011-10-22, 12:39 AM
http://www.mrexcel.com/forum/images/buttons/reportpost.gif (http://www.mrexcel.com/forum/report.php?p=2905259)
http://www.mrexcel.com/forum/images/icons/icon1.gif Help with sum formula

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,


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


2011-10-25, 10:39 AM
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?

2011-12-15, 12:45 PM
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

2011-12-15, 11:36 PM
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:

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

2011-12-15, 11:59 PM
I glazed over the part where you said you're new to excel. Check out this link for info on using a UDF and where to put the code, http://www.cpearson.com/Excel/WritingFunctionsInVBA.aspx. If you post a sample workbook I can add the code and repost.