Mastering SUMIFS Formula: College Expenses Example

Excel’s SUMIF and SUMIFS formulas are ideal for tracking college expenses by student, year, and type. I’ll show you how to use SUMIFS to sum costs like tuition, fees, and more.

Overview of SUMIF and SUMIFS Formulas

Excel’s SUMIF function allows you to sum data based on a single condition, while SUMIFS lets you use multiple conditions.

Though SUMIFS is more complex, it’s also more flexible and adaptable, making it ideal even for one criterion (in case you need to add others later).

[embed]

Using SUMIFS Formula: College Expenses Example

Here’s how to set up the SUMIFS formula with multiple criteria. We’ll be organizing college expenses based on student name, academic year, and type of expense.

Knowing I have three criteria, I’ll set up my SUMIFS formula with three criteria ranges to match.

Selecting SUMIFS Multiple Criteria

Step 1: Define the Sum Range

• Goal: Select the range of data to be summed based on the criteria.
• In this example, we’ll use Column J on the “Cost” tab.
• Use an absolute reference for this range to ensure the sum range remains consistent when copied across cells.

Step 2: Set the First Criterion: Student

• Goal: Include only expenses for the selected student, Michael.
• Select Column B for the first criteria range, where students’ names are listed.
• Set the criterion as cell C1 on the dashboard tab. Use an absolute reference here so the cell reference doesn’t change when copying the formula across years.

Step 3: Set the Second Criterion: Academic Year

• Goal: Filter by academic year (e.g., Freshman).
• Set the second criteria range to Column C, which contains the students’ academic year.
• Link the criterion to cell C5, which holds the academic year. To make the formula dynamic, use an absolute reference for the row only, allowing the column to change when copied across (C$5).

Step 4: Set the Third Criterion: Expense Type

• Goal: Further narrow down by type of expense, like “Tuition.”
• For the third criteria range, select Column H, where the types of expenses are categorized.
• Link this criterion to cell B6 and make the column absolute, so it remains fixed when copying the formula down ($B6).

Applying and Copying the Formula Across Cells

After completing the SUMIFS formula, copy it across and down to calculate expenses for each combination of student, year, and expense type. The SUMIFS formula is perfect for automatically summing data based on your chosen criteria.

Using SUMIF for a Single Criterion: Quick Comparison

When you only need to sum data based on a single criterion, like total expenses for Michael, the SUMIF formula can be used. Here’s how it’s done:

Example: SUMIF Formula for Single Criterion

• Select the expense column ($J:$J).
• Set the criterion as Michael’s name ($C$1), being tested in Column B (Costs!$B:$B), on the Costs Tab.

The order of arguments in SUMIF differs from SUMIFS, so the sum range comes last in SUMIF.

Even with just one criterion, we can use the SUMIFS formula. This approach makes it easy to expand the formula if more conditions are needed later and keeps your formulas consistent, especially in complex worksheets.

Summary

With Excel’s SUMIF and SUMIFS formulas, tracking expenses by specific categories becomes straightforward and flexible. For example, you can easily track college expenses by category, student, and year.

Get College Expenses & Payments Worksheet on Etsy

Originally published at https://akistepinska.com.