The sum of which numbers match total

[Mircea Gabriel]

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

 

[jackhandey]

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

 

[PaddyD]

Hi - welcome to the board.

You can use sover for this. Example here:

http://www.mrexcel.com/forum/excel-questions/22298-multi-summing-check-register.html

The hall of fame link in there is broken, but post back with more info abut where your data is etc if you can't get it working.

 

[jackhandey]

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,"")

 

[Mircea Gabriel]

Finally I found a solution.Your suggestions led me to this website.

Combination of numbers that sum or match a target value

Thanks guys,
Mircea

 

[PaddyD]

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