Forum Discussion
Simple Excel Formula
I have a basic spreadsheet which shows all my bills which come out of my account. I want to create a running total but as I don't get paid on a set date every month I'm struggle to create a formula which shows this. I want to place a "Y" or "N" in a column and this will update the total for what I have left to pay. I'm not very good at formulas so any help would be appreciated. The spreadsheet is attached if that helps.
Hi Dan
Thanks for uploading the spreadsheet, it makes it easier to see what you want to do.
I've created a formula to help with your calculation and have also added some suggested improvements to your original layout and formulas for some of the other cells:
- Conditional formatting to automatically highlight if the Paid is Y (green) or N (red).
- Total Bills paid now sums any amounts that are Y.
=SUMIF(D3:D14,"Y",C3:C14) - Added a Total remainder owing cell (C17) to show what is still unpaid/owing.
=SUMIF(D3:D14,"N",C3:C14) - Put a new formula in the Total (C21) cell to calculate you Income minus Total Owning to give you a final Total.
=SUM(C19:C20)-C17 - Have applied conditional formatting the final Total cell (C21) to show green if your income is more than your outstanding balance, or red if you owe more money than you have.
- Moved Paid column next to column C (Amount) for easy viewing.
Everything attached in your modified file is an optional extra. Was just having a bit of fun on this Friday afternoon!
I hope something in the attached helps. Let us know how you go?
Cheers
Damien
- Conditional formatting to automatically highlight if the Paid is Y (green) or N (red).
3 Replies
Hi Dan
Thanks for uploading the spreadsheet, it makes it easier to see what you want to do.
I've created a formula to help with your calculation and have also added some suggested improvements to your original layout and formulas for some of the other cells:
- Conditional formatting to automatically highlight if the Paid is Y (green) or N (red).
- Total Bills paid now sums any amounts that are Y.
=SUMIF(D3:D14,"Y",C3:C14) - Added a Total remainder owing cell (C17) to show what is still unpaid/owing.
=SUMIF(D3:D14,"N",C3:C14) - Put a new formula in the Total (C21) cell to calculate you Income minus Total Owning to give you a final Total.
=SUM(C19:C20)-C17 - Have applied conditional formatting the final Total cell (C21) to show green if your income is more than your outstanding balance, or red if you owe more money than you have.
- Moved Paid column next to column C (Amount) for easy viewing.
Everything attached in your modified file is an optional extra. Was just having a bit of fun on this Friday afternoon!
I hope something in the attached helps. Let us know how you go?
Cheers
Damien
No problems Dan. Glad to help!
Have a great weekend and all the best.
Cheers
Damien
- Conditional formatting to automatically highlight if the Paid is Y (green) or N (red).