Sum If, If Two Numbers Equal a Specified Total

Hello,

I hope you can help me with the following problem I am encountering.

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 there any formula that can tell me if any 2 numbers in each line add up to a 3rd number?

For instance, in

4-7-11-12-40-42-56-83, 4 and 7 add up to 11

So I would like to get the a “Yes” or “X” when this happens.

If the above is not possible, I don’t mind any other solution that would give me an indication that the above event exists in a line.

Just to be honest, I have posted a question to this problem in another forum but I have not gotten a reply.

Thank you in advance

Sans

5 thoughts on “Sum If, If Two Numbers Equal a Specified Total

  1. Excel Geek

    Hi there

    Try this VBA code. You will have to copy and paste it into a module for it to work and then call it via the macro player. I have assumed your eight numbers are spread across columns A to H, and that the answer of Yes if an eleven is possible is displayed in column I. If your columns are different you will need to change a few lines of code…

    Sheets("Sheet1").Select
    Change the name between ” ” to the name of your worksheet.

    For X = 1 To 10 Step 1 'Change the 10 to the number of rows you have
    Change the 10 to the number of rows you have

    For Y = 1 To 8 Step 1
    Change the 1 and the 8 to the columns containing your numbers. For example 1 is column A and 8 is column H. I would be 9 and so on.

    Cells(X, 9) = "Yes"
    Change the 9 to the column you want the “Yes” displayed. So 9 is I, 10 would be J and so on.

    The code is below…

    Sub SumCheck()

    Sheets("Sheet1").Select

    Dim X As Long
    Dim NumberArray(7) As Long

    For X = 1 To 10 Step 1 'Change the 10 to the number of rows you have

    'Loads the array of numbers
    For Y = 1 To 8 Step 1
    NumberArray(Y - 1) = Cells(X, Y)
    Next Y

    'Checks the sums by running through each cell and row
    B = 1
    For A = 1 To 7 Step 1
    For B = B To 7 Step 1
    If NumberArray(A - 1) + NumberArray(B) = 11 Then
    Cells(X, 9) = "Yes"
    GoTo NextRow
    End If

    C = C + 1

    Next B
    B = B - C + 1
    C = 0
    Next A

    NextRow:
    Next X

    End Sub

    I have also attached my Excel workbook that contains the above VBA code in case it helps.
    I hope this helps.

    Thanks.

  2. Sans

    Thank you very much for your reply.

    I think may have been a little vague in my first post. I’d like to get a YES whenever -any two- numbers add up to another number in the line.

    9 13 14 11 15 29 1 10 should be “Yes” because 10+1=11

    But also 10 13 23 2 17 15 5 26 should be “Yes” because 10+13=23.

    In any one line there are no duplicate numbers and the actual range of numbers goes from 1 -60.

    Thanks again

  3. Excel Geek

    No problem try this…

    Sub SumCheck()

    Sheets("Sheet1").Select

    Dim X As Long
    Dim NumberArray(7) As Long

    For X = 1 To 10 Step 1 'Change the 10 to the number of rows you have

    'Loads the array of numbers
    For Y = 1 To 8 Step 1
    NumberArray(Y - 1) = Cells(X, Y)
    Next Y

    'Checks the sums by running through each cell and row
    B = 1
    For A = 1 To 7 Step 1
    For B = B To 7 Step 1

    'Checks each answer against the sum of each possible two numbers
    For D = 1 To 7 Step 1
    If NumberArray(D - 1) = NumberArray(A - 1) + NumberArray(B) Then
    Cells(X, 9) = "Yes"
    GoTo NextRow
    End If

    Next D

    C = C + 1

    Next B
    B = B - C + 1
    C = 0
    Next A

    NextRow:
    Next X

    End Sub

  4. Sans

    Hi,
    Thank you so much, it works perfectly. This is exactly what I was looking for. You’ve saved me so much time.

    I can’t thank you enough!

Leave a Reply

Your email address will not be published. Required fields are marked *