When it comes to the often tedious task of preparing for tax season, staying organized is imperative to success. Understanding what expenses you’ve incurred over the past year, and how impactful each expense was to your finances, can help you maximize deductions.

Knowing what percentage of your total household income is coming from each source will help ensure that you not only submit accurate numbers when filing time comes around, but also that you can thoroughly eke out every possible deduction. Thankfully, Excel offers a suite of tools and functions that can help you get organized while doing your taxes.

1. Categorizing Expenses by Amount in Excel

Keeping your expenses organized is essential when preparing to do your taxes. To optimize your return, you’ll need to itemize your major purchases made over the past year.

It’s entirely possible to go through each line item on your list manually to check what qualifies as a major expense. To save time, however, you can automate your calculations by totaling expense categories based on price with Excel’s SUMIF function.

Consider the following spreadsheet representing example data from a full year of spending.

A year-long list of expenses for a single household categorized by type.

Getting totals for major and minor expenses is simple with a pair of SUMIF functions.

A year-long expense list for a single household with totals for major expense and minor ones.

The F2 cell uses the following SUMIF function to total all expenses greater than $300.00:

        =SUMIF(B2:B28, ">300")
    

Meanwhile, the F3 cell is also using its own SUMIF function to add all expenses less than, or equal to $300.00:

        =SUMIF(B2:B28, "<=300")
    

Together, these two functions simplify calculating how much you spent on major or minor purchases.

2. Totaling Expense Categories by Type in Excel

In addition to dividing expenses by purchase size, you may also wish to calculate totals by expense category. This can allow you to find the totals for items that may be deducted from your tax liability, and may be necessary to enter on some forms.

The SUMIF function makes calculating the totals by expense type simple. It allows the check function to operate on one range while a separate range gets summed.

A year-long expense list for a single household with totals for the Housing, Donations, and Business Expenses categories.

In cells F5:F7, we use the following array of functions to get the totals for individual categories:

        =SUMIF(C2:C28, "=Housing", B2:B28)
    
        =SUMIF(C2:C28, "=Donations", B2:B28)
    
        =SUMIF(C2:C28, "=Business", B2:B28)
    

The SUMIF function runs on the C column containing the category. The third argument, however, specifies what column to add. In this case, you want to add all the values in column B, provided the type in column C is the right type. This allows us to use the SUMIF function for purposes apart from numerical ones.

3. Using Excel’s SUMIF to Categorize Income by Type

Using the same skills from the last item on the list, you can perform similar calculations on your income. Swap finding your Donations and Business expenses for finding capital gains, interest accruals, and contractor income.

If you have more than one type of income that fits into a specific category, you can use the SUMIFS function to check multiple conditionals and run sums across multiple categories. This is handy for those with holdings accruing interest, capital assets that have gained value over the course of the year, or large investment portfolios.

As an example, we will look at a spreadsheet listing off income over the course of a year categorized by source. Each item on the list contains an amount, date, and type. Next to the list, a set of SUMIF functions are being used to total each income type so that the totals can be taxed appropriately.

A year-long income report for a single household with totals for each source of income.

In the above example, column G is populated using the following function:

        =UNIQUE(D2:D52)
    

This provides a breakdown of every unique type of income listed in column D. This allows column H to be populated with a series of SUMIF functions that reference the cells in column G. For example, the following function in cell H2 provides the total of all items of type “income”.

        =SUMIF(D2:D52,G2,B2:B52)
    

This provides a sum for each unique type of income listed. Each type of income is taxed at a different rate, making calculating what is owed on each difficult without totals.

4. Itemizing Deductions With Excel’s SUMIF

Another great use case for Excel’s SUMIF function is totaling deductions. Many expenses throughout the year can be counted against your taxable income. The simplest way to keep track is to create a separate sheet just for counting your deductions, then start summing different types.

Creating this list will rely on you having good records elsewhere, so it’s important that your other info is complete. These can save you a considerable amount of cash on your taxes, so keeping up with putting in records throughout the year is vital. Doing so can save you time on going back through old receipts.

Given a list of expense types that can be counted toward deductions, you can quickly total them, like in the example below.

A list of yearly expenses for a single household with deductions calculated.

This example uses a list of deductible expenses on another sheet, along with the percentage of expenses, to calculate the total for each deduction category. After each category is determined, the results are added for a total deduction. Each category total is determined with this formula:

        =SUMIF(C2:C28,E2,B2:B28)*Sheet2!B1
    

Column B on Sheet 2 contains the category's deductible percentage. Finally, a grand total can be simply calculated with this function:

        =SUM(F2:F4)
    

5. Using SUMIF to Calculate Totals Based on Date in Excel

Excel’s SUMIF function can compare more than just equality between two pieces of text. It can also make date comparisons and even tell whether a given date came before or after another.

Totaling by date can be handy for cases where a major life change happened throughout the year, such as a move or career shift. Knowing what information occurred before and after can help you calculate what’s owed.

The ideal way to sum by category over a fixed date range is by using Excel’s SUMIFS function. It is similar to the standard SUMIF; however, it can have multiple conditionals defined in it:

        =SUMIFS(B2:B28, C2:C28, "Housing", D2:D28, "<= " & DATE(2022, 2, 5))
    
A set of yearly expenses for a household with a total calculated before a cutoff date.

In the above function, we assume that a move occurred from one state to another on 02/05/2022. This will total all payments for housing that happened on or before that day in the “Housing” category.

First, this method validates that the category in C2:C28 is correct, then validates that the date in D2:D28 is within the correct time frame. Additionally, if necessary, you can add more filters, like summing totals above a specific amount or ignoring items with a specific name.

The SUMIFS function is convenient for checking more than one condition on a single record, since it can accept as many tests as needed.

Excel’s SUMIF Can Help You Fully Customize Tax Prep

Excel is a simple yet effective way to organize your records when preparing for taxes. This tool is indispensable, with built-in ways to automatically calculate totals, keep expenses categorized, and determine how much you’ve made in income, interest, and dividends.

As an added bonus, it's capable of helping you save cash on itemized expenses and calculate income for various frames of time. Together, these make Excel’s SUMIF function invaluable for anyone preparing their own taxes.