# 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.

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`

Thanks.

2. Sans

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!