# Budget/debt sheet help!!!!

#### Heatherleveridge

##### New Member
Ok so I have my monthly bills on one sheet and my overall debt on the second sheet and I want some way to tell excel when I pay a monthly bill and it automatically take that amount off the total debt on the second page. I cannot figure out a way to tell excel when I make a payment.
So in my photos I have it written next to Navient how much I owe each month on sheet one, but I want some way to tell excel when I pay them. And once I mark that it’s paid have a formula on the second sheet under total amount left that will automatically subtract it.

#### Attachments

• naviant.PNG
16.3 KB · Views: 5
• amount left.PNG
5.5 KB · Views: 5

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### Joe4

Welcome to the Board!

You should be able to use a SUMIF formula in order to get the total of the payments for any particular entity on the "monthly bills" sheet.
(See: MS Excel: How to use the SUMIF Function (WS)).

So, if you have your starting balance stored in some cell somewhere, it would just be a formula structured like:
=starting balance - SUMIF(...)

If you need help setting it up, please provide us with exact details, such as what columns the entity names and amounts are in, and then what cells those are in on the "debt" sheet, as well as the cell address where the starting balance is stored.

#### R2ah1ze1l

##### Board Regular
Seems to me you may need a column or cell group that you can 'trigger' to cause your calculations.
Maybe make a column for 'Date Paid' while the cell is "blank" no calculations occur, when a date is entered 1/1/21 the calculation occurs.
It is kind of clunky but if you are only using a personal worksheet it will get you through.

Code:
``````=if(cell="","",subtract paid sum from total avail)
^location of date``````

Alternatively, just enter the value paid in an empty column and subtract the listed value from your available balance.

HTH

#### Heatherleveridge

##### New Member
Welcome to the Board!

You should be able to use a SUMIF formula in order to get the total of the payments for any particular entity on the "monthly bills" sheet.
(See: MS Excel: How to use the SUMIF Function (WS)).

So, if you have your starting balance stored in some cell somewhere, it would just be a formula structured like:
=starting balance - SUMIF(...)

If you need help setting it up, please provide us with exact details, such as what columns the entity names and amounts are in, and then what cells those are in on the "debt" sheet, as well as the cell address where the starting balance is stored.
Starting balance is in cell 'Overall Debt'!D12 and I am putting a date paid column in and the date paid will be in cell 'Monthly Bills'!D11

I am having trouble writing this formula I keep getting an error message. So if I am reading this right the excel formula will be something like =IF('Monthly Bills'!D11, ",", subtract ='Monthly Bills'!C11 from 'Overall Debt'!D12)

* ='Monthly Bills'!C11 is where I have the amount of the bill I am paying.
Also thank you so much for your help.

#### Heatherleveridge

##### New Member

Seems to me you may need a column or cell group that you can 'trigger' to cause your calculations.
Maybe make a column for 'Date Paid' while the cell is "blank" no calculations occur, when a date is entered 1/1/21 the calculation occurs.
It is kind of clunky but if you are only using a personal worksheet it will get you through.

Code:
``````=if(cell="","",subtract paid sum from total avail)
^location of date``````

Alternatively, just enter the value paid in an empty column and subtract the listed value from your available balance.

HTH
cell address where the starting balance is stored.
Starting balance is in cell 'Overall Debt'!D12 and I am putting a date paid column in and the date paid will be in cell 'Monthly Bills'!D11

I am having trouble writing this formula I keep getting an error message. So if I am reading this right the excel formula will be something like =IF('Monthly Bills'!D11, ",", subtract ='Monthly Bills'!C11 from 'Overall Debt'!D12)

* ='Monthly Bills'!C11 is where I have the amount of the bill I am paying.
Also thank you so much for your help.

#### Joe4

I am afraid your explanation does not seem to quite make sense, when coupled with your pictures. Can you post an actual example, where we can see the column and row numbers, and let us know in that example you have posted what your expected result is?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

#### Heatherleveridge

##### New Member

I am afraid your explanation does not seem to quite make sense, when coupled with your pictures. Can you post an actual example, where we can see the column and row numbers, and let us know in that example you have posted what your expected result is?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
I apologize for being unclear, I am just starting to dive into the Excel world as of recently. I could not figure out how to download the XL2BB on my computer (it could be because this is my company issued computer)

 December​ Date Paid​ January​ Date paid​ February​ March​ April​ May​ June​ July​ Naviant \$157.45​ \$157.45​ \$157.45​ \$157.45​ \$157.45​ \$157.45​ \$157.45​ \$157.45​ Discover Card \$26.00​ \$33.00​ Capital One 2 \$100.00​ \$190.00​ Auto Loan \$150.00​ \$269.10​ \$239.10​ Original Creditor Total Amount Left Interest rate Payoff Date (if only paying minimums) Starting amount Naviant \$7,789.49​ 9.00%​ 2/26/2026​ \$7,789.49​ Capital One 2 \$785.56​ 24.74%​ 1/28/2024​ \$785.56​ Discover \$304.00​ 22.99%​ 2/28/2022​ \$304.00​ Auto Loan \$4,843.41​ 22.36%​ 10/29/2022​ \$4,843.41​ *What I am looking for is a formula that will calculate B9-B2 once I apply a paid date in C2.

#### Attachments

• Excel Help 2.PNG
93.9 KB · Views: 2

#### Joe4

The structure of the sheet seems very odd. You seem to have a "Date Paid" column after the months of December and January, but not any of the other months.
So I am not sure how the logic is supposed to work. Are you adding "Date" Paid" for the other months at a later point?
Do you have any control over the structure of these sheets? I think the current structure might make this much harder to do than it has to be.

#### Heatherleveridge

##### New Member
The structure of the sheet seems very odd. You seem to have a "Date Paid" column after the months of December and January, but not any of the other months.
So I am not sure how the logic is supposed to work. Are you adding "Date" Paid" for the other months at a later point?
Do you have any control over the structure of these sheets? I think the current structure might make this much harder to do than it has to be.
Yes I planned on adding a date paid column to the other months as well. Also I have complete control over it, what would you suggest would help improve the layout/structure?

#### Joe4

what would you suggest would help improve the layout/structure?
Anything where you are constantly having to add new columns is going to be a huge pain to maintain. You may have to update your formulas every time, unless you come up with some elaborate formula or VBA solution.

I think it would be better to have a structure of just 4 columns for the payment history, with these columns:
Entity
Payment for Month
Payment Date
Payment Amount

Then as new payments are being made, you don't need to add new columns, just new rows.
This sort of structure would be very conducive to using SUMIF (or SUMIFS) functions like I described in my first reply.

Replies
1
Views
57
Replies
0
Views
598
Replies
2
Views
62
Replies
2
Views
82
Replies
9
Views
2K

1,129,917
Messages
5,638,981
Members
417,061
Latest member
thematulaak

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

### Which adblocker are you using?

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

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