# The sum of which numbers match total

#### Mircea Gabriel

##### New Member
Hello,

For about a week,I have been strugulling on this problem.I'm hoping that you guys can give me hand.

I have a column of numbers "n1,n2,n3...n1000" and a total "x".What i'm triying to find is,the sum of which numbers from the column
equals to "x",and highlight them.

For example:

total

x=6230

column

n1=1121
n2=1524
n3=3668
n4=1441
n5=5689

this is a column of 5 numbers;basicly you try to add the numbers manually to find a match.

n1+n3+n4=x

What about a column of 1000 numbers.It takes forever...

Any ideas ?

Thanks,
Mircea

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### jackhandey

##### Board Regular
This can certainly get complicated quickly. Will it always be 3 numbers exactly that add up to the X value, or is it a random number of numbers from the list that have to be identified?

I think I have an idea if it's always 3 specific numbers... maybe

#### jackhandey

##### Board Regular
I have a feeling that this is too simple for what Mircea is looking for... but it was fun to figure it out.

Assuming your data above is in cells B1:B5 and the X value is in B10, paste this into A1. It will return a 1 if the adjacent cell in column B is one of three numbers that sum together to equal the X value.

This will only work if there are exactly 3 numbers that add up to X.

It's an array formula, so enter with CTRL+SHIFT+ENTER and copy down to the bottom of the number range. From there, you can use simple conditional formatting to highlight as you see fit.

=IF(PRODUCT((B1-IF(COUNTIF(\$B\$1:\$B\$5,\$B\$10-\$B\$1:\$B\$5-TRANSPOSE(\$B\$1:\$B\$5)),\$B\$1:\$B\$5,0)))=0,1,"")

##### MrExcel MVP

"For about a week,I have been strugulling on this problem"

Don't let it happen again After no more than an hour of head scratching, come here...

Replies
1
Views
60
Replies
4
Views
108
Replies
11
Views
247
Replies
7
Views
170
Replies
3
Views
86

### Forum statistics

1,129,685
Messages
5,637,809
Members
416,983
Latest member
LessThanAverageUser

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back