How SUMIFS works in Excel? Best SUMIFS examples

How SUMIFS works in Excel? Best SUMIFS examples

When working in Excel, I often need to sum up values based on multiple criteria. That’s where SUMIFS comes in handy. Unlike the basic SUMIF, which allows for only one condition, SUMIFS lets me apply several conditions at once. Let’s dive deep into how it works and explore some of the best SUMIFS examples.

Understanding the SUMIFS Function

The SUMIFS function in Excel allows me to sum values that meet multiple criteria. The syntax looks like this:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Here’s what each argument means:

  • sum_range: The range of numbers I want to sum.
  • criteria_range1: The range where I’m applying the first condition.
  • criteria1: The condition that should be met in criteria_range1.
  • criteria_range2, criteria2: Additional criteria pairs (optional).

Basic Example of SUMIFS

Let’s say I have sales data and I only want to sum the revenue for a specific product and a specific region.

Product Region Revenue
Phone North 500
Laptop South 700
Phone South 300
Laptop North 900

If I want to sum the revenue for all Phone sales in the South region, I’d use:

=SUMIFS(C2:C5, A2:A5, "Phone", B2:B5, "South")

The result? 300, because only one row matches both criteria.

Using SUMIFS with Date Criteria

Often, I need to sum values within a date range. Suppose I have a list of transactions:

Date Category Amount
2024-01-05 Food 30
2024-02-10 Transport 50
2024-03-15 Food 20

To sum all expenses in the “Food” category between January 1, 2024, and February 28, 2024, I’d use:

=SUMIFS(C2:C4, B2:B4, "Food", A2:A4, ">=2024-01-01", A2:A4, "<=2024-02-28")

The result? 30, since the entry from March 15 is outside the date range.

SUMIFS with Wildcards

Wildcards help me when I need to sum based on partially matching text. I use:

  • * to match any number of characters.
  • ? to match a single character.

For example, if I have a list of city names and sales:

City Sales
New York 1000
New Orleans 800
Los Angeles 1200

If I want to sum sales for all cities starting with “New”, I’d use:

=SUMIFS(B2:B4, A2:A4, "New*")

The result? 1800, because both “New York” and “New Orleans” match.

Common Mistakes with SUMIFS

When using SUMIFS, I’ve encountered a few common pitfalls:

  1. Using mismatched ranges: The sum_range and criteria_range must be the same size.
  2. Forgetting quotation marks: Text-based criteria need to be enclosed in quotes.
  3. Incorrect date formatting: Dates must be formatted properly; otherwise, Excel may misinterpret them.

Conclusion

The SUMIFS function is a powerful tool when I need to sum values based on multiple conditions. Whether I’m working with text, numbers, or dates, understanding how to properly structure the function saves me tons of time. Hopefully, these examples make it clear how SUMIFS works in Excel!

 

Other interesting article:

How SUMIF works in Excel? Best SUMIF examples