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.

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.

=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.

=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.

=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.

=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

• 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.