Sums values across a range based on multiple criteria applied to corresponding ranges, enabling governed multi-condition aggregation without array formulas. The default aggregation pattern for every DDL dashboard.
SUMIFS answers the question every dashboard asks: add up the numbers in this column, but only the rows that match these conditions. Region equals West, year equals 2026, status equals Closed — sum the revenue. SUMIFS does this in one function, with no helper columns and no array formulas. It is the most-used aggregation function in DDL workbooks for a reason: it’s the simplest possible expression of multi-criteria math.
Here’s a real SUMIFS. Click each argument to see what it does and how the pairs work.
This is the column whose values get added up. SUMIFS will only add values from rows that satisfy every criteria pair that follows. Notice it’s the first argument — the inverse of SUMIF, where the sum_range is last. This argument order trips up everyone exactly once. After that, you remember.
Criteria come in pairs: a range to look in, and a value to match against. The first item (Sales[Region]) is the column to inspect; the second ("West") is the value the row must have in that column to be included in the sum. Each pair narrows the matching rows further — SUMIFS uses AND logic across all pairs.
A second pair narrows the matching rows further. Now we’re only summing rows where Region is "West" and Year is 2026. Numeric criteria can be literal values, cell references, or comparison strings like ">1000" or "<="&Threshold.
A third pair narrows further. Now we’re summing only the Revenue from rows where Region = "West", Year = 2026, and Status = "Closed". You can keep adding criteria pairs — SUMIFS supports up to 127 of them — though if you find yourself approaching that number, you should be using Power Query instead.
The same aggregation — one needs three helper columns and a SUM, the other is one formula.
-- 3 helper columns + a SUM
H2: =IF(AND(
A2="West",
B2=2026,
C2="Closed"),
E2, 0)
=SUM(H2:H10000)
Three helper columns. One SUM. Logic spread across two places. Helper columns must be maintained as data grows. Audit nightmare.
=SUMIFS( Sales[Revenue], Sales[Region], "West", Sales[Year], 2026, Sales[Status], "Closed" )
One formula. No helper columns. The criteria are visible in plain text. Anyone reading the formula understands what it returns without exploring the workbook.
SUMIFS (and its sibling COUNTIFS) is the default aggregation pattern for DDL dashboards. Reach for SUMPRODUCT only when SUMIFS cannot express the condition — for example, when criteria require calculation across multiple columns at once. Always reference table columns through structured references, never raw ranges.
When you need to aggregate values based on two or more conditions across different columns, use COUNTIFS or SUMIFS to perform governed multi-criteria counting or summation.
Criteria ranges must be identical in size to the sum_range or SUMIFS returns #VALUE!. Text criteria are case-insensitive by default — "Closed" matches "closed", which can cause unexpected matches in dirty data. Wildcard characters (* and ?) in criteria strings are interpreted as wildcards, so a criterion like "7*" matches anything starting with 7, not the literal text "7*".
Faster than SUMPRODUCT for simple multi-criteria aggregation. Use SUMIFS over SUMPRODUCT when all criteria are direct column comparisons. SUMPRODUCT is only justified for calculated or cross-array criteria that SUMIFS cannot express.
COUNTIFS/SUMIFS is the default aggregation pattern for DDL dashboards. Use SUMPRODUCT only when SUMIFS cannot express the condition. The Selector-Driven Reconfiguration pattern (ARC-0003) uses SUMIFS internally. Criteria values should reference Control Panel cells, never be hardcoded inside the formula.
SUMIFS function · COUNTIFS · multi-condition-sum · governed-aggregation
-- Total revenue for one region in one year =SUMIFS( Sales[Revenue], Sales[Region], "West", Sales[Year], 2026 ) -- One number. Both filters explicit. No helper columns.
-- Same syntax. No sum_range. Returns the row count instead of the sum. =COUNTIFS( Orders[Status], "Pending", Orders[DaysOpen], ">30" ) -- How many orders have been pending more than 30 days? -- The ">30" is the criterion, not a separate argument. -- Comparison operators always live inside the criteria string.
-- Pull criteria values from named cells on the Config tab =SUMIFS( Sales[Revenue], Sales[Region], Config!RegionFilter, Sales[Year], Config!YearFilter, Sales[Status], Config!StatusFilter ) -- Change the filter on the Config tab and the -- entire dashboard updates. No formula edits. -- This is the foundation of Selector-Driven Reconfiguration.
-- A "% of target" KPI built from two SUMIFS calls =LET( actual, SUMIFS(Sales[Revenue], Sales[Region], "West", Sales[Year], 2026), target, SUMIFS(Targets[Goal], Targets[Region], "West", Targets[Year], 2026), pct, IFERROR(actual / target, 0), pct ) -- The KPI Card pattern (PTN-0014) uses exactly this shape: -- two named SUMIFS calls and a final ratio.