The sum of which numbers match total

Mircea Gabriel

New Member
Joined
Jan 7, 2014
Messages
2
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
Joined
Aug 2, 2013
Messages
74
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
Joined
Aug 2, 2013
Messages
74
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,"")
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Glad you've got a solution.

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

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top