What formula would suit to have monthly amounts entered in my account worksheet

Question

I need a formula that would enter these amounts every month/quartley/yearly in my current account w/sheet

Answer 1

Hello @William Auld

To create an automated summary in your account worksheet, combining SUMIF and XLOOKUP is the suitable way to manage your budget.  

1/ The Functions:  

• SUMIF (The "Organizer"): This function looks at your entire list, picks out everything labeled "Monthly", and adds them all together. It is perfect for seeing your total budget at a glance. 
• XLOOKUP (The "Finder"): Think of this as a search tool. It looks for a specific name (like "Microsoft 365") and pulls the exact price into your main sheet. This is great for checking individual bills without scrolling through a long list. 

2/ Practical Formulas for your Sheet 

To get started, you can use these formulas based on the table in your image: 

To get your Total Monthly Outgoings: 

=SUMIF(H:H, "Monthly", G:G) 

SUMIF function - Microsoft Support

To find the price of a specific bill (e.g., Amazon Prime): 

=XLOOKUP("Amazon-Prime", F:F, G:G)

XLOOKUP function - Microsoft Support

You can try my suggestion. If it doesn’t meet your needs, please come back with screenshots or any additional details so I can review it again and see if there’s anything more I can help you with.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in "our documentation" to enable e-mail notifications if you want to receive the related email notification for this thread. 

Answer 2

Use a date formula that adds the correct interval to the current “DueNext” date, based on the text in the “When” column. For example, for the “In” table:

Assume:

• C2 = When (Monthly/Quartly/Yearly)
• D2 = current DueNext date

In D2 (and copy down) use:

=IF(C2="Monthly",
     EDATE(D2,1),
 IF(C2="Quartly",
     EDATE(D2,3),
 IF(C2="Yearly",
     EDATE(D2,12),
 D2)))

Explanation:

• EDATE(start_date, months) adds whole months to a date.
• Monthly: EDATE(D2,1) → next month
• Quartly: EDATE(D2,3) → 3 months later
• Yearly: EDATE(D2,12) → 12 months later

Use the same pattern for the “Out” table, pointing to the appropriate “When” and “DueNext” columns.

Whenever a payment is made, overwrite the old DueNext date with the formula result (or keep the original in another column and use this formula in a “Next Due” column).

---

References:

• Enter a formula
• Create a formula by using a function
• Using Excel formulas to figure out payments and savings