SUMIFS function in Excel

This is the part you'll use most. Ninety percent of "just pull me the numbers" requests come down to one move. Total a column, but only for the rows where some condition holds. Sometimes it's a count or an average instead. Think revenue in the West region, invoices from Maharashtra, wages in Engineering, overdue balances past 90 days. Each of the five plural functions answers that in a single line. You won't need a PivotTable. You won't need to sort or filter the source either:

• SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
• COUNTIFS(criteria_range1, criteria1, ...) counts rows meeting all conditions. It has no sum range, because there's nothing to sum.
• AVERAGEIFS(average_range, criteria_range1, criteria1, ...)
• MAXIFS(max_range, criteria_range1, criteria1, ...) and MINIFS(...), the largest/smallest value among matching rows (Excel 2019+).

How do SUMIFS arguments and criteria work?

The one rule that prevents the most common error in Excel. In the plural SUMIFS, the sum range comes first, and the criteria follow in (range, criterion) pairs. In the singular, legacy SUMIF(range, criteria, [sum_range]), the range you test comes first and the sum range is an optional third argument. The two are mirror images. The plural form is strictly better because it takes many conditions, and its argument order holds across the whole *IFS family. COUNTIFS, AVERAGEIFS and MAXIFS all put their aggregate range first. So use the plural forms only, even for a single condition. SUMIFS(B:B, A:A, "West") is one keystroke longer than SUMIF(A:A,"West",B:B), and it never trips you on argument order. That's the professional default.

Mechanics that matter.

• Criteria can be values, cell references, or logical text. =SUMIFS(Sales, Region, "West") hard-codes the criterion. =SUMIFS(Sales, Region, G2) reads it from a cell, so a summary block updates as you type. Always prefer the cell reference. Comparison operators go inside quotes. =SUMIFS(Amount, DueDate, "<"&TODAY()) totals amounts due before today. Look at the "<"& there. The operator is text, joined on to the value.
• Multiple conditions are AND. =SUMIFS(Sales, Region, "West", Quarter, "Q3") totals only rows that are West and Q3. Say the West/Q3 amounts are 120, 80 and 95, with other rows in East, North or Q2. That formula returns 295. COUNTIFS on the same conditions returns 3, AVERAGEIFS returns 98.33 and MAXIFS returns 120.
• Wildcards work in text criteria. "*Ltd" matches anything ending "Ltd"; "???" matches exactly three characters; escape a literal * or ? with a tilde ("~*").
• All ranges must be the same size and shape. If they aren't, SUMIFS returns a #VALUE! error. Living with that rule also keeps your data rectangular.

How do you build a cross-tab with SUMIFS?

They earn the name "workhorses" because they compose. Put state codes down the side of a small grid and quarters across the top, and make each cell a SUMIFS with two criteria. What you have is a cross-tab. It's a pivot table you built by hand and fully control. It recalculates live, and you can point formulas at it. You'll build one in the worked examples.