1. Add and Subtract Income and Expenses: SUM
The SUM function is one that you’ll use the most when it comes to finances in Excel. It allows you to add numbers, cells that contain numbers, or a combination of both. You can use the SUM formula in your budget for totaling your income and adding your expenses.
RELATED: How to Add Numbers in Microsoft Excel
The syntax is SUM(value1, value2,...) where value1 is required and value2 is optional. You can add the formula manually or use a handy button to insert it.
To total up your income for the year, select the cell where you want the result. Click the AutoSum button in the Editing section on the Home tab.
Confirm or edit the cell range that displays and press Enter or Return.
Alternatively, you can type the following formula replacing the cell references with your own:
=SUM(C5:N5)
You can also use the SUM function to subtract values in Excel. You’ll likely want to subtract your total expenses from your total income to see how much you have left.
Go to the cell where you want the result and enter the following replacing the cell references with your own:
=SUM(C7-C17)
Alternatively, you can enter the following using the minus sign operator, without the SUM function involved:
= C7-C17
2. Add Certain Expenses: SUMIF
Similar to SUM, the SUMIF function allows you to add numbers that meet specific criteria. You can use this to add the amounts due for your loans or maybe those bills your roommate pays.
The syntax is SUMIF(cell_range, criteria, sum_range) and the first two arguments are required. The third argument, sum_range, is useful if you want to add numbers in one range of cells that correspond to criteria in another range.
Here, we want to add expenses listed in cells C10 through C17 only if those amounts are labeled Loan in cells B10 through B17.
=SUMIF(B10:B17,"Loan",C10:C17)
You can use the SUMIF formula to match criteria for text as we did here, but also for numbers.
In this example, we’ll add the expenses in cells D10 through D17 but only those that are over $400.
=SUMIF(C10:C17,">400")
3. Find the Highest or Lowest Expense: MIN or MAX
When you’re keeping track of bills in your budget, you might want to see the highest values. This allows you to adjust for upcoming months or years. MAX shows you the highest value while MIN shows you the lowest.
The syntax for each is MAX(value1, value2...) and MIN(value1, value2...) where the values can be numbers or cell ranges. Plus, you can enter the formula manually or use the SUM drop-down arrow and select it.
To see the highest amount for an expense during the year, select the cell where you want the result. Click the arrow next to AutoSum in the Editing section on the Home tab and pick “MAX.”
Confirm or edit the range of cells in the formula and press Enter or Return.
To enter the formula manually, use the following replacing the cell references with your own:
=MAX(C12:N12)
You can use MIN the same way to find the lowest value. Select MIN from the SUM drop-down list or enter the formula manually.
=MIN(C12:N12)
4. Count Expenses or Payments: COUNT
Want to know how many bills you pay each month or the number of paychecks you receive throughout the year? Using the COUNT function, you can count how many cells contain numbers.
RELATED: How to Count Cells in Microsoft Excel
The syntax is COUNT(value1, value2,...) where value1 is required. Like SUM, you can use a button or manually enter the formula.
Select the cell where you want the result. Click the arrow next to AutoSum in the Editing section on the Home tab and pick “Count Numbers.”
Confirm or edit the cell range that displays and press Enter or Return.
Alternatively, you can type the following formula replacing the cell references with your own:
=COUNT(C10:C17)
5. See How Many Days to Pay: DAYS
If part of your budget is seeing how many days you have between when you get paid and when a bill or loan payment is due, the DAYS function does exactly that.
The syntax is DAYS(end_date, start_date) with both arguments required. You can use dates or cell references.
To find the number of days between our end date (due date) in cell B3 and start date (pay day) in cell A3, we would use this formula:
=DAYS(B3,A3)
To find the number of days between specific dates rather than cell references, you would use the following formula. Enclose the dates in quotes and remember that the end date comes first:
=DAYS("1-DEC-2022","1-MAR-2022")
6. See How Many Business Days to Pay: NETWORKDAYS
Similar to the DAYS function, NETWORKDAYS counts the number of work (or business) days between two dates. This resulting number excludes weekends and recognized holidays.
The syntax is NETWORKDAYS(start_date, end_date, holidays) where the dates are required and holidays is optional to include a cell range with exclusions.
To find the number of business days between our start date (pay day) in cell A3 and our end date (due date) in cell B3, we would use this formula:
=NETWORKDAYS(A3,B3)
7. View the Current Date: TODAY
As you work on your budget, the current date is important. Without looking at the calendar, you can display the current date in your sheet and see it updated each time you open the workbook.
The syntax is TODAY() with no arguments. Simply enter this formula into the cell and hit Enter or Return.
=TODAY()
If you want to create your own budget in Excel rather than use a template, these functions have you off to a great start. You might also take a look at how to use Money in Excel to track your bank accounts and loan balances.
0 Comments