Customized sum

Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

How do you arrive at this result?

In D1: 4556.92

someone said to me, I know whatever you said but actually i need the sum of that amount with the formula
suppose these are my outstanding amount and someone pay the 4556.92, then I want to see which invoices amount the concerned paid

What do you mean? Do you seriously believe that the sum of A2 to A55 is 4556.92? It isn't - it's over 35000!!!

Type this into D1:

=SUM(A2:A55)

So, let's start again: what EXACTLY are you trying to do here?

So you have said twice now. Doesn't matter - unless I know how 'someone' has arrived at that number, I can't help you.

Look:

Excel 2016 (Windows) 32 bit

	A	B	C	D
1				35,994.20
2	77.74
3	83.06
4	89.40
5	116.14
6	126.69
7	144.77
8	160.62
9	185.58
10	192.65
11	194.58
12	219.10
13	222.52

Sheet: Sheet4

Originally Posted by geniusufo007

someone said to me, I know whatever you said but actually i need the sum of that amount with the formula
suppose these are my outstanding amount and someone pay the 4556.92, then I want to see which invoices amount the concerned paid

Do NOT edit posts that have received replies - your edits will mostly go unnoticed.

Which values in your list do you think make up that amount? Is this homework?

I give up - you just don't answer my questions.

Good luck with this.

Just play along

=SUM(A2:A55)-SUM(A2:A55)+D1

my guess is that the OP wants to know which items in column A between A2 and A55 would be added together to get 4556.92.

Yes, and I asked about just that in post #8 - the question was ignored. My guess is that the OP has been given this to work out - it's probably a homework task. I asked that, too - guess what? No response.

I have some code running to test that. So far, it's found 642 combinations that total 4556.92, with 0.4% of the combinations tested.

Now 809. There's an average of over 5,000,000 solutions to any possible result. I can explain why, but it's a little math-y.

Now 1,186.

1370 ... 1508 ... 2304 ... 2543 at 1% complete ...

I wondered if it could be done by a formula, I think it is an interesting problem and have been trying to contemplate how the formula would look.
Now, based on shg's comment I see it is likely a code based solution.

This looks like a subset sum problem (https://en.wikipedia.org/wiki/Subset_sum_problem ). As shg's post notes, it is usually solved by some variation of "try every possible combination and identify which one(s) add up to the resulting sum. The main difficulty is that there are a lot (millions or billions or trillions or more, depending on the specific problem) of possible combinations to try, so that the computational effort needed to find all possible solutions and try them is often huge.

Most "first attempt" excel solutions to the subset sum problem try using Solver: https://www.get-digital-help.com/201...lver-in-excel/
Most rigorous attempts will use VBA to generate all possible combinations -- something like this http://www.tushar-mehta.com/excel/te...e_combinations

Conceptually, the subset sum problem seems simple, but, as an NP-complete type problem, it is surprisingly difficult to program a rigorous solution into a computer.

19,085 at 4.7% complete

That gives one solution; what about the others?

30,319 at 6.5% complete.

Thanks Mr Shorty for your attempt

This has been marked as solved - can you please confirm that it is?