When working with data in Excel, you’ll often need to sum up values across a range of cells. But what if you need to add those values based on specific conditions? That’s where the SUMIF and SUMIFS functions in Excel come into play.

In this guide, I’ll walk you through the key differences between SUMIF and SUMIFS, how to use them, and give you a practical example you can apply to your own spreadsheets. Let’s dive right in.

SUMIF vs SUMIFS Functions in Excel: What’s the Difference?

Both functions help you sum data, but they are useful in different situations depending on how many conditions you need to apply.

  • SUMIF: This is used when you only have one condition.
  • SUMIFS: This comes into play when you need to sum data based on multiple conditions.

Let’s break each function down and explore them further.

Understanding the SUMIF Function

The SUMIF function is straightforward. It lets you add values based on a single condition. For instance, imagine you have a list of sales data and you want to sum only the sales related to a specific product, like “apples.”

Here’s a practical step-by-step guide:

Example: Adding Sales for a Single Item (Apples)

You have a table with these columns:

  • Item Description column: List of items sold (like “apples,” “oranges”)
  • Dollar Amount column: The total for each sale.

To sum sales for “apples”:

  1. Click on the cell where you want the sum result to appear.
  2. Type the formula =SUMIF.
  3. Define the criteria range: This is where Excel will look for your condition, which is “apples”. Highlight this range, in this case, your Item Description column (Let’s say F2:F200).
  4. Set the condition to “apples.”
  5. Define the sum range, which is the Dollar Amount column (H2:H200).
  6. Hit Enter.

Excel will sum all sales under the item “apples.” The total amount is shown in your chosen cell.

Formula example:

=SUMIF(F2:F200, "apples", H2:H200)

In a few clicks, you’ve added up all the relevant sales for apples. But what if you need more than one condition?

RELATED TOPIC: How to Use the IF Function in Excel

Using the SUMIFS Function for Multiple Conditions

Here’s where SUMIFS comes in. Excel lets us go beyond one condition and sum data based on several criteria.

For example, let’s say you want to sum sales of apples within a specific date range. You’ll need SUMIFS.

Example: Summing Sales by Item and Date Range

Here’s how to do it:

  1. Click on the cell where you want the final sum.
  2. Type =SUMIFS. This time, Excel asks for the sum range first.
  3. Select the Dollar Amount column as the sum range (H2:H200).
  4. Next, choose your first criteria range: We’ll use the Item Description column again (F2:F200).
  5. Set your first criteria to “apples”.
  6. Now, choose the next criteria range, which will be the Date column (B2:B200).
  7. For this second condition, you’ll want a date range. Let’s say you want all data from January 1st, 2020 to June 30th, 2020. You’ll input:
    • Criteria 2: Date ≥ “1/1/2020” (or reference a cell with this date)
    • Criteria 3: Date ≤ “6/30/2020” (or reference another cell for this).

The formula will look like this:

=SUMIFS(H2:H200, F2:F200, "apples", B2:B200, ">="&M10, B2:B200, "<="&N10)

This tells Excel: “Sum the sales amount for apples, only if the date is between January 1st and June 30th, 2020.”

You can modify the dates by changing the cell references (M10 and N10).

RELATED TOPIC: How to Use the VLOOKUP Function in Excel

Why Use Cell References for Dates?

Using a cell reference like M10 instead of typing the date directly into the formula gives you flexibility. You can easily change the date ranges later without touching the formula itself.

For example, if you want to adjust the range to look only at February instead of the entire six months, just change the values in the date reference cells.

Avoid Common SUMIFS Pitfalls

When working with multiple conditions, data setup is critical. It’s easy to make mistakes when copying and pasting formula parts, especially if you have many criteria. Always double-check that each range matches correctly with its corresponding condition.

If you accidentally copy the wrong range, Excel won’t give you clear errors all the time. Instead, the calculation returns an unexpected result, making it harder to find the problem later on.

Wrapping Up

To recap:

  • Use SUMIF for simple single-condition summing.
  • Use SUMIFS when you have multiple conditions, like summing sales for a specific item within a date range.

Mastering these functions will save you time and make your data analysis more accurate. Remember, you can always reference cells for flexible date ranges and experiment with more advanced criteria as your skills grow.

Want more Excel tutorials? Subscribe to my channel to stay up to date with new lessons and tips.

If this tutorial was helpful, don’t forget to like this post and share it with others who could benefit from learning more about how to use SUMIF and SUMIFS functions in Excel.

Get Started with Microsoft 365

Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.

This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.

RELATED ARTICLESMORE FROM AUTHOR