How to autosum numerous data in a column and find a specific number.

**ExcelQuestionHelp** · 07-26-2013, 11:39 AM

Hi, I'm new to this thread but have a question that could really help with a project I am working on.

To describe the project, I am going through numerous different company invoices and marking off the specific ones as they are paid so we know what invoices remain unpaid. I go about this by getting a check such as $21,992.21. I then go to the company invoice section and find that this $21,992.21 can be made up of many different payments. I copy all the payments and paste them into excel going through and highlighting them together to see which ones will add up to $21,992.21.

This isn't that bad when the invoices look like...

5,435.82
5,428.83
3,761.74
5,447.71
5,467.98
5,530.92
5,545.60
4,242.71
4,242.71

Because I can highlight easy go through and see that the 4th 5th 6th and 7th add up to $21,992.21. It gets more difficult when I have a company that has 100 + payments so the process takes much longer.

Is there a formula that may autosum each column and then I can use "command F" to search for the invoice check amount in order to find these specific payments faster?

Thank you for any help

**Pauleyb** · 07-26-2013, 12:14 PM

Are the payments always grouped (e.g. 4th, 5th, 6th, and 7th) or can they be scattered (e.g. 4th, 8th, and 28th)?
Do you know the number of invoices that make up an check. I realize one check could be 4 invoices and another could be 15, but would you know as you are looking at the check?
There is nothing you describe in excel, but there may be ways to make this easier.

**ExcelQuestionHelp** · 07-26-2013, 12:19 PM

The payments are usually grouped together but there are ocassions in which the payments are scattered within the companies invoices. And no, when looking at the check there is no way to know how many different payments it may consist of. As of now I've just been going through one by one adding in Excel to see when I hit the amount on the check.

Thanks

**Pauleyb** · 07-26-2013, 12:42 PM

Unfortunately, as you describe it, this is called an np-complete problem in the programming world. Essentially, the program would have to try every combination to determine a match (and, there could be multiple matches). Actually, it is not quite np-complete since you don't have to try every combination, since you could stop once you find a match, but it is still a lot of number crunching. A VBA routine could be written to perform this task, but it would take some time (albeit, maybe faster than you when confronted with 100s of invoices).

Honestly, you need a better invoicing system, or you need to ask more from your accountants. When your company gets a check, there should be some reference to the invoices. Maybe in the payment notice to the customer? A list of closed invoices? In this case, there could be more than one way to add up the numbers to the check quantity, and you could 'close' the wrong invoices. Maybe, you don't care about which invoices get closed, since the dollar amount would still be correct, but that then implies SOMEONE in your comany is keep track of which invoices have been truly had the services rendered and payments received. Talk to them...