SUMIFS Function

Summary

The Excel SUMIFS function sums values that satisfy multiple conditions. Unlike SUMIF, which tests only one condition, SUMIFS uses AND logic across a series of criteria-range pairs. A row is added to the total only if every condition is true for that row.

That structure makes SUMIFS one of the main reporting functions in Excel. It is used when a workbook needs totals such as one product in one region, one department in one month, or one account category above a threshold. The function is restrictive by design: it narrows the total to the intersection of several filters.

SUMIFS becomes useful when the worksheet needs a total that depends on several rules at once. It is a natural fit for business reporting because real totals often depend on multiple conditions such as category, period, region, or status all at the same time.

Syntax

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

The most important structural point is that sum_range comes first. After that, criteria are supplied in pairs: a range to test and the condition applied to that range. Microsoft documents up to 127 range-criteria pairs, which gives the function broad coverage for most report logic.

Arguments

• sum_range - [Required] The cells to add.
• criteria_range1 - [Required] The first range to test.
• criteria1 - [Required] The condition applied to the first range.
• criteria_range2, criteria2, ... - [Optional] Additional range-condition pairs.

SUMIFS vs Related Functions

SUMIFS extends the logic of SUMIF by allowing several simultaneous filters. It is still a conditional aggregation function, but its role is specifically to support AND-based reporting across multiple fields.

SUMIFS is not an OR-logic tool by default. If the worksheet must sum values matching condition A or condition B, that usually requires multiple SUMIFS formulas added together or a different formula structure.

Using SUMIFS

SUMIFS is most effective when the source data is organized as a single normalized table with one row per record and separate columns for each reporting dimension. In that format, each criterion range represents one field, and the sum range represents the measure to aggregate.

It is also common in date-range analysis. A month, quarter, or fiscal-period total can be created by placing two conditions on the date column: one lower bound and one upper bound. That approach is often more reliable than trying to match a month label stored as text.

• Keep all criteria ranges aligned with the sum range.
• Use quoted operators for numeric criteria such as ">150".
• Use two date conditions on the same column when defining a date interval.

Example 1 - Sum Where Two Conditions Both Match

This example shows the core logic of SUMIFS. A row is counted only if it matches both the product criterion and the region criterion. Rows matching only one of them are excluded from the total.

This is a strong beginner example because it shows the “all conditions at once” rule clearly. The formula is not building two separate totals, but one total from the rows where both tests are true together.

=SUMIFS(C1:C5,A1:A5,"Apples",B1:B5,"North")
// Matching rows: C1 = 100 and C4 = 300
// Total = 400

Example 2 - Combine a Category with a Threshold

SUMIFS can mix text and numeric logic in the same formula. Here the row must belong to the Apples category and also exceed the numeric threshold. This pattern appears often in report segmentation and exception analysis.

That makes the example useful for real reporting. A worksheet often needs more than a label match alone, so the formula shows how to add a threshold on top of a category filter.

=SUMIFS(C1:C5,A1:A5,"Apples",C1:C5,">150")
// Only the Apples row with value 300 qualifies
// Total = 300

Example 3 - Use Cell-Driven Criteria

Referencing criteria from cells turns the formula into a reusable reporting component. A dashboard or parameter area can change the values in G1 and G2, and the SUMIFS result updates without modifying the formula itself.

This is practical because the report can change with user input. Instead of rewriting the formula for a new product or region, the user only changes the criterion cells.

=SUMIFS(C1:C5,A1:A5,G1,B1:B5,G2)
// G1 = "Apples", G2 = "South"
// Matching row: C3 = 150

Example 4 - Filter One Group While Excluding Another

Equality and inequality can be combined in the same SUMIFS call. This allows the worksheet to isolate a subset while explicitly excluding one category inside that subset. The result is often clearer than subtracting one subtotal from another.

This helps show that SUMIFS can narrow a total from both directions at once. One condition picks the group you want, and another condition removes the part you do not want.

=SUMIFS(C1:C5,B1:B5,"North",A1:A5,"<>Apples")
// Matching row: C2 = 200
// Total = 200

Range alignment is the main structural risk with SUMIFS. If the criteria ranges do not match the shape of the sum range, the result can become unreliable. For that reason, disciplined table structures and consistent column references are especially important when a workbook depends heavily on SUMIFS.

• SUMIFS uses AND logic, not OR logic.
• The sum range comes first, unlike SUMIF.
• All criteria ranges should match the dimensions of the sum range.

Conclusion Recap

SUMIFS is for questions that need more than one filter at the same time. This lesson kept coming back to one simple rule: a row is added only when every condition is true, so the function works with AND logic, not OR logic.

The examples showed how useful that is for real reports. You can combine product, region, thresholds, exclusions, or cell-based filters in one formula. The main thing to remember is the structure: sum range first, then the condition pairs after it.

• Summary: SUMIFS sums values that satisfy multiple conditions.
• Syntax: =SUMIFS(sum_range,criteria_range1,criteria1,...).
• Key behavior: Every condition must be true for a row to be included.
• Practical usage: Multi-field reporting, date-range totals, and segmented analysis.