Excel Formula Help

[Katec]

Hi Experts!

I'm hoping someone can assist with a formula that I hope Excel can do! We have a customer who sends us money for payment through ACH. They have several invoices with us but all we receive is a total amount in our bank account and no remittance advice. Is there an Excel formula where you can plug in the open invoices and the total paid and Excel can determine what amounts add up to the total paid? (hopefully this description makes sense)

Thank you so much in advance!

Kate

 

[Legacy 436357]

Hi,

I am not sure what you want but maybe this can be a start:

Excel Workbook
	A	B	C	D	E
1	Invoices	Amount	Paid	Balance	Open/Closed
2	1	$ 500.00	$ 100.00	$ 400.00	Open
3	2	$ 700.00	$ 700.00	$ -	Closed
4	3	$ 800.00	$ 500.00	$ 300.00	Open
5	4	$ 900.00	$ 900.00	$ -	Closed
6	5	$ 1,050.00	$1,050.00	$ -	Closed
7	6	$ 1,180.00	$1,180.00	$ -	Closed
8	7	$ 1,310.00	$1,300.00	$ 10.00	Open
9	8	$ 1,440.00	$1,000.00	$ 440.00	Open
10	9	$ 1,570.00	$1,980.00	$ 1,200.00	Open
11	10	$ 1,700.00	$1,700.00	$ -	Closed
Sheet1

 

[Katec]

Thank you for your quick response. I'm looking for something more like this:

[TABLE="width: 339"]
<colgroup><col width="132" style="width: 99pt;"><col width="87" style="width: 65pt;"><col width="120" style="width: 90pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 132"]Open Invoices[/TD]
[TD="width: 87"][/TD]
[TD="class: xl63, width: 120"]Amount Paid[/TD]
[/TR]
[TR]
[TD="class: xl64"] $ 5,231.00 [/TD]
[TD][/TD]
[TD="class: xl64"] $ 6,094.43 [/TD]
[/TR]
[TR]
[TD="class: xl64"] $ 7,459.00 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"] $ 307.69 [/TD]
[TD][/TD]
[TD="class: xl63"]Invoices Paid[/TD]
[/TR]
[TR]
[TD="class: xl64"] $ 4,956.00 [/TD]
[TD][/TD]
[TD="class: xl64"] $ 5,231.00 [/TD]
[/TR]
[TR]
[TD="class: xl64"] $ 201.99 [/TD]
[TD][/TD]
[TD="class: xl64"] $ 307.69 [/TD]
[/TR]
[TR]
[TD="class: xl64"] $ 555.74 [/TD]
[TD][/TD]
[TD="class: xl64"] $ 555.74 [/TD]
[/TR]
[TR]
[TD="class: xl64"] $ 3,219.44 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
So for this, we would enter in all of their open invoices, the amount paid, and excel would determine which amounts out of the open invoices equaled the amount paid. Hopefully, I explained myself better!

 

[Legacy 436357]

Okay how about like this:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Invoice #	Open Invoices	Paid Amount		Open/Closed			Amount Paid
2	1	$5,231.00	$0.00	$5,231.00	Open			$6,094.43
3	2	$7,459.00	$7,459.00	$0.00	Closed
4	3	$307.69	$0.00	$307.69	Open		Status	Invoices Paid
5	4	$4,956.00	$4,956.00	$0.00	Closed		Open	$5,231.00
6	5	$201.99	$201.99	$0.00	Closed			$307.69
7	6	$555.74	$0.00	$555.74	Open			$555.74
8	7	$3,219.44	$3,219.44	$0.00	Closed
Sheet1

 

[Katec]

I'm so sorry, I feel like you are doing everything to help and I'm not getting the answer because I'm not explaining myself! For your table, I know the information in Column B and I know the amount paid. From that, I want Excel to tell me Column C. For instance if I enter a total of $6094.43, I want excel to tell me which amounts the customer paid or populate column C. Basically, out of the open invoices which ones equal the total paid.

 

[Legacy 436357]

Maybe what you need is "Excel Solver"

 

[Dave Patton]

If the volume of numbers is not too great, the manual process shown below can help.

Solver and/or solutions cited below can help.

Excel 2010
	B	C	D	E
2	Open Invoices		Amount Paid
3	$5,231.00	1	$6,094.43	TRUE
4	$7,459.00
5	$307.69	1
6	$4,956.00
7	$201.99
8	$555.74	1
9	$3,219.44
10
2d

Cell Formulas
Range	Formula
E3	=SUMPRODUCT(B3:B9,C3:C9)=D3

There was a good discussion on this years ago. It may have been part of an Excel Challenge.
I do not have the reference.


Check
Which numbers add up to a specific total

VBA Sub by Harlan Grove

Find a set of amounts that match a target value
By Tushar Mehtahttp://www.tushar-mehta.com http://www.tushar-mehta.com/excel/software/match_target/

 

[Legacy 436357]

I have tried to use the solver from this link:

https://www.extendoffice.com/docume...nd-all-combinations-that-equal-given-sum.html

The solver puts the "1" and "0" in column B.

Then I altered the sheet using it:

Sheet1

	A	B	C

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:129.6px;"><col style="width:97.6px;"><col style="width:150.4px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: center"]Open Invoices[/TD]
[TD="align: center"]Solver Results[/TD]
[TD="align: center"]Amount Paid[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: center"]$5,231.00 [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$6,094.43 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: center"]$7,459.00 [/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: center"]$307.69 [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Invoices Paid[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: center"]$4,956.00 [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]$5,231.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: center"]$201.99 [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]$307.69[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: center"]$555.74 [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$555.74[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: center"]$3,219.44 [/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: center"]$6,094.43[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C2 =B9 C5 {=IFERROR(INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=1,ROW($B$2:$B$8)-ROW($B$2)+1),ROWS($B$2:$B2))),"")} C6 {=IFERROR(INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=1,ROW($B$2:$B$8)-ROW($B$2)+1),ROWS($B$2:$B3))),"")} C7 {=IFERROR(INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=1,ROW($B$2:$B$8)-ROW($B$2)+1),ROWS($B$2:$B4))),"")} C8 {=IFERROR(INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=1,ROW($B$2:$B$8)-ROW($B$2)+1),ROWS($B$2:$B5))),"")} B9 =SUMPRODUCT(A2:A8,B2:B8) C9 {=IFERROR(INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=1,ROW($B$2:$B$8)-ROW($B$2)+1),ROWS($B$2:$B6))),"")} C10 {=IFERROR(INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=1,ROW($B$2:$B$8)-ROW($B$2)+1),ROWS($B$2:$B7))),"")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4