Heatherleveridge

New Member
Joined
Dec 31, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.
Please help!
 

Attachments

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

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,913
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 10, 2017
Messages
67
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
Joined
Dec 31, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 31, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,913
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 31, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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 CreditorTotal Amount LeftInterest ratePayoff 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
    Excel Help 2.PNG
    93.9 KB · Views: 2

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,913
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 31, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,913
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,458
Messages
5,636,383
Members
416,917
Latest member
koto1

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?

Disable AdBlock

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
Top