# Excel Formula Help

#### Katec

##### New Member
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

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
L

#### Katec

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

 Open Invoices Amount Paid \$ 5,231.00 \$ 6,094.43 \$ 7,459.00 \$ 307.69 Invoices Paid \$ 4,956.00 \$ 5,231.00 \$ 201.99 \$ 307.69 \$ 555.74 \$ 555.74 \$ 3,219.44

<colgroup><col width="132" style="width: 99pt;"><col width="87" style="width: 65pt;"><col width="120" style="width: 90pt;"></colgroup><tbody>
</tbody>
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!

#### Katec

##### New Member
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.

L

#### Legacy 436357

##### Guest
Maybe what you need is "Excel Solver"

#### Dave Patton

##### Well-known Member
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
BCDE
2Open InvoicesAmount Paid
3\$5,231.001\$6,094.43TRUE
4\$7,459.00
5\$307.691
6\$4,956.00
7\$201.99
8\$555.741
9\$3,219.44
10
2d
Cell Formulas
RangeFormula
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/

Last edited:
L

#### Legacy 436357

##### Guest
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 1 Open Invoices Solver Results Amount Paid 2 \$5,231.00 1 \$6,094.43 3 \$7,459.00 0 4 \$307.69 1 Invoices Paid 5 \$4,956.00 0 \$5,231.00 6 \$201.99 0 \$307.69 7 \$555.74 1 \$555.74 8 \$3,219.44 0 9 \$6,094.43 10

<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>
</tbody>

 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

Last edited by a moderator:

Replies
3
Views
436
Replies
3
Views
478
Replies
4
Views
326
Replies
0
Views
143
Replies
15
Views
2K

1,195,592
Messages
6,010,621
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back