Find if there is a sum inside a range that gives a value of x

[petar256]

Hi all


Is it possible to find the result of a sum of numbers in some other column. I am attaching a workbook example.

Thank you.

[AlKey]

Maybe this?

Please Login or Register  to view this content.

OR

Please Login or Register  to view this content.

[Zer0Cool]

I have a feeling this is one of those "Sum of numbers equal target value" posts.

@petar256 can you give us an example of what the expected result is? Its unclear what it is you are trying to accomplish.

[alansidman]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[petar256]

Hi

I will try to rephrase;

I have to find the amount of each cell in column B is it somewhere in column A?

So if there is a sum of x numbers in column A that gives a result that is in column B i want to do something.

[Zer0Cool]

Ah ok, so I was right its "Find x values who's sum is y".

This is a very simple question but is mathematically very complex. While Excel can do it, it very quickly gets to a point that its not feasible to use Excel. The complexity is also effected by any constraints on the solution.

So for example: Are we looking for the first combination (sum) that matches the target value? Do we care how we find a combination of values whos sum is equal to target (largest values = target, fewest values = target, etc)?

Some constraints mean we get to stop looking sooner, other constraints mean we need to exhaust checking all possible combinations.

I presume also that for your purposes we can only use a value in Column A 1 time? IE: we cannot do 100+100+100 (being that 100 is in the column only 1 time)?

Taking the example you gave, with 6 values in A and 3 in F, the worst case scenario (needing to check all possible combinations) is roughly:

40,000 possible combinations per value in F or 120,000 total combinations (presuming we arent checking for all 3 target values at the same time which could potentially drop us down to 40,000 ish).

The number of x possible combinations very quickly gets out of hand with the more values you have and I am presuming your actual file has more values than your sample.

[petar256]

I was kind of expecting this..Yea..original file has over 10000 rows with diferent values...

think im gonna have to find different solution.

Thanks anyway

[Zer0Cool]

Yea 10,000 rows would likely take a lifetime. I saw 30 values still going after 24 hours before (maybe with some conditions I dont recall).

I dont know how, as I dont do Access, but I know that there is a way it can do this very fast compared to Excel (as we ended up using it for a customer instead of Excel in the past). Might be worth researching.