# 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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,184
Messages
5,857,827
Members
431,900
Latest member

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