Study guide
8.3 Multi-Criteria Aggregations: SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, & MINIFS
Microsoft Excel Expert Microsoft 365 Apps
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Exam Overview & MO-211 Fundamentals
1.1 MO-211 Exam Structure, Scoring, & Performance-Based Strategy 1.2 Excel Environment & Microsoft 365 Architecture
2 Chapter 2: Managing Workbooks & File Options
2.1 Workbook Versioning, AutoRecover, & File Properties 2.2 External Data Referencing & Cross-Workbook Formulas 2.3 Macro Security, Portability, & Workbook Distribution
3 Chapter 3: Workbook Protection & Collaboration Settings
3.1 Cell Locking, Range Permissions, & Worksheet Protection 3.2 Workbook Structure Protection & Editing Restrictions 3.3 Formula Calculation Options & Iterative Calculations
4 Chapter 4: Advanced Data Entry & Series
4.1 Flash Fill Patterns & Text Extraction Techniques 4.2 Advanced Fill Series (Linear, Growth, Date, & Step Configurations) 4.3 Dynamic Sequence & Random Data Generation (RANDARRAY, SEQUENCE)
5 Chapter 5: Custom Formatting & Data Validation
5.1 Custom Number Formatting Syntax & Masking Rules 5.2 Data Validation Criteria & Custom Formula Rules 5.3 Validation Input Messages, Error Alerts, & Data Circle Auditing
6 Chapter 6: Data Grouping, Summarization, & Cleansing
6.1 Data Grouping, Ungrouping, & Multi-Level Outlining 6.2 Subtotal Insertion & Summary Function Calculations 6.3 Duplicate Record Removal & Text-to-Columns Cleansing
7 Chapter 7: Advanced Conditional Formatting & Filtering
7.1 Formula-Based Conditional Formatting & Logical Tests 7.2 Data Bars, Color Scales, Icon Sets, & Rule Hierarchy 7.3 Advanced Filtering with Complex Criteria Ranges
8 Chapter 8: Logical Operations & Advanced Multi-Condition Functions
8.1 Nested IF, IFS, & SWITCH Logical Structures 8.2 Compound Boolean Logic: AND, OR, NOT, & XOR 8.3 Multi-Criteria Aggregations: SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, & MINIFS
9 Chapter 9: Advanced Lookup Functions & Variable Optimization
9.1 Modern Lookups with XLOOKUP & XMATCH 9.2 Classic Matrix & Two-Way Lookups: INDEX & MATCH 9.3 Formula Optimization & Variable Assignment with LET
10 Chapter 10: Date, Time, Text, & Auditing Functions
10.1 Current Date, Time, & Day-of-Week Functions: NOW, TODAY, & WEEKDAY 10.2 Business Date Calculations: WORKDAY, NETWORKDAYS, EDATE, & EOMONTH 10.3 Modern Text Extraction & Array Splitting: TEXTSPLIT, TEXTJOIN, TEXTBEFORE, TEXTAFTER 10.4 Error Handling & Formula Auditing Tools
11 Chapter 11: Dynamic Array Functions & Spill Operations
11.1 Array Filtering & Extraction: FILTER Function 11.2 Multi-Level Sorting: SORT & SORTBY Functions 11.3 Deduplication & Sequence Arrays: UNIQUE & Array Reshaping
12 Chapter 12: Data Analysis, What-If Tools, & Financial Functions
12.1 Summarizing Multiple Ranges with the Consolidate Feature 12.2 What-If Analysis: Goal Seek & Scenario Manager 12.3 Financial Modeling Functions: PMT, NPER, & Forecast Logic
13 Chapter 13: Macro Automation & VBA Fundamentals
13.1 Recording Macros with Absolute vs. Relative References 13.2 Macro Storage, Shortcut Assignment, & Form Control Buttons 13.3 Managing & Modifying VBA Code in the Visual Basic Editor
14 Chapter 14: Advanced & Specialized Visualizations
14.1 Statistical Visualizations: Histograms & Pareto Charts 14.2 Hierarchy & Financial Visualizations: Waterfall, Funnel, Treemap, & Sunburst 14.3 Distribution & Multi-Axis Charts: Box & Whisker & Dual-Axis Combo Charts
15 Chapter 15: PivotTable Construction & Field Configuration
15.1 PivotTable Data Architecture, Layouts, & Style Configuration 15.2 Field Summarization, Number Formatting, & Custom Calculations 15.3 Value Display Modes: % of Total, Running Total, & Differences
16 Chapter 16: PivotTable Grouping, Slicers, & PivotCharts
16.1 Date & Numeric Field Grouping Strategies 16.2 Calculated Fields & Calculated Items in PivotTables 16.3 Slicers, Timelines, Report Connections, & PivotCharts
8.3 Multi-Criteria Aggregations: SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, & MINIFS
Key Takeaways Explain This Topic AI
SUMIFS, AVERAGEIFS, MAXIFS, and MINIFS place the calculation range as their first argument (sum_range, average_range, max_range), whereas legacy single-criterion SUMIF and AVERAGEIF place the calculation range last.
All criteria_range arguments in multi-condition aggregation functions must have the exact same dimensions (identical row and column counts) as the calculation range, or Excel returns a #VALUE! error.
When combining logical comparison operators (>, <, >=, <=, <>) with cell references or nested formulas, the operator must be enclosed in quotation marks and concatenated with an ampersand (e.g., ">="&E1).
Wildcard characters (*, ?, ~) enable pattern matching across text criteria ranges, where asterisk represents any character string, question mark represents a single character, and tilde escapes wildcards.
Multi-criteria aggregation functions inherently apply AND logic across criteria pairs; to implement OR logic across multiple values in a single field, supply an array constant (e.g., {"North","South"}) and wrap the function inside SUM().
Last updated: September 2026
8.3 Multi-Criteria Aggregations: SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, & MINIFS
Enterprise data analysis requires extracting targeted quantitative summaries from large transactional datasets without restructuring source tables or building heavyweight PivotTables. When summarizing ledgers, an analyst frequently needs to sum revenue for a specific product line within a target territory during a precise calendar quarter, or compute the average margin while filtering out outliers and returned orders. Excel provides a dedicated suite of multi-criteria aggregation functions: SUMIFS , COUNTIFS , AVERAGEIFS , MAXIFS , and MINIFS . Mastering their argument syntax, criteria expression formatting, wildcard rules, dimensional constraints, and array constant techniques is essential for the MO-211 examination.
Syntax Architecture: SUMIF vs. SUMIFS & The Multi-Condition Family
Excel maintains two generations of conditional aggregation functions: legacy single-criterion functions ( SUMIF , COUNTIF , AVERAGEIF ) and modern multi-criteria functions ( SUMIFS , COUNTIFS , AVERAGEIFS , MAXIFS , MINIFS ).
The Argument Order Reversal Trap
The single most prevalent exam trap on MO-211 involves the structural inversion of arguments between single-criterion and multi-criteria functions:
Legacy SUMIF Syntax : =SUMIF(range, criteria, [sum_range]) In SUMIF , the condition range comes first, and the numeric calculation range ( sum_range ) is the final, optional argument. If omitted, Excel sums the range itself.
Modern SUMIFS Syntax : =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) In SUMIFS , the calculation range ( sum_range ) comes first . It is followed by up to 127 pairs of criteria ranges and their associated criteria. SUMIF Architecture:
[ criteria_range ] ──► [ criteria ] ──► [ sum_range (optional, last) ]
SUMIFS Architecture:
[ sum_range (mandatory, first) ] ──► [ criteria_range1 ] ──► [ criteria1 ] ──► [ criteria_range2 ] ──► [ criteria2 ]
The same leading calculation range structure applies across the entire multi-criteria family:
- =AVERAGEIFS(average_range, criteria_range1, criteria1, ...)
- =MAXIFS(max_range, criteria_range1, criteria1, ...)
- =MINIFS(min_range, criteria_range1, criteria1, ...)
=COUNTIFS(criteria_range1, criteria1, ...) (Has no calculation range; it counts rows where all criteria evaluate to TRUE ) .
Dimensional Consistency Requirement
Every criteria_range supplied to a multi-criteria function must have the exact same row and column dimensions as the calculation range (or the first criteria_range in COUNTIFS ).
If sum_range spans D2:D100 (99 rows), writing A2:A99 (98 rows) or A2:B100 (2 columns) as a criteria_range causes Excel to return a #VALUE! error immediately. The ranges do not need to reside in adjacent columns, but their bounding dimensions must be identical.
Criteria Expression Syntax, Operators, & Concatenation
Criteria arguments evaluate each cell in the corresponding criteria_range . Excel supports several criteria formats:
- Literal Values and Text Matching
- Exact numbers: =COUNTIFS(B2:B100, 25)
Exact text strings: =SUMIFS(D2:D100, A2:A100, "North") (text comparisons are case-insensitive).
Case sensitivity note: "north" , "NORTH" , and "North" match the same records.
Comparison Operators & Dynamic Cell Concatenation
When filtering by numeric thresholds, dates, or dynamic cell references using relational operators ( > , < , >= , <= , <> ), the operator must be enclosed in double quotation marks.
If referencing a static value: =COUNTIFS(C2:C100, ">=50")
If referencing an external cell (e.g., cell F1 containing the cutoff threshold), the operator must be enclosed in quotes and joined to the cell reference using the ampersand ( & ) concatenation operator : =SUMIFS(D2:D100, C2:C100, ">="&F1)
Critical Exam Pitfall: Entering ">=F1" literally checks whether cells contain text greater than or equal to the string "F1", rather than evaluating against the value inside cell F1 . Omitting the quotes entirely ( >=F1 ) triggers a formula syntax error.
Wildcard Matching & Empty Cell Evaluation
For text-based criteria ranges, Excel provides three wildcard characters:
Wildcard Definition Example Pattern Matches Does Not Match
* (Asterisk) Matches any sequence of characters (including zero characters) "Corp*" "Corp", "Corporation", "Corporate" "Unicorp"
- ? (Question Mark) Matches exactly one single character "B?ll" "Ball", "Bell", "Bill", "Bull" "Bollard"
- ~ (Tilde) Escapes a literal wildcard character ( ~* , ~? , ~~ ) "Total~*" "Total*" "Total Revenue"
- Evaluating Blanks and Non-Blanks
To count or filter cells that are completely blank: "" or "=" .
To filter for non-blank cells: "<>"&"" or "<>" .
Note on zeroes in AVERAGEIFS : Cells containing numeric 0 are included in the average calculation, whereas blank cells in average_range are excluded. If an analyst needs to calculate the average excluding both zeroes and blanks, add an explicit criterion: =AVERAGEIFS(D2:D100, A2:A100, "North", D2:D100, "<>0")
Multi-Dimensional Date Range Slicing & Boundary Filtering
A common requirement in financial reporting is aggregating transactions within a specific date window (e.g., fiscal Q1 between January 1, 2026, and March 31, 2026). Because Excel multi-criteria functions apply implicit AND logic across all criteria pairs, date range filtering is accomplished by referencing the date column twice with lower and upper boundary conditions: =SUMIFS(D2:D500, A2:A500, ">="&G1, A2:A500, "<="&G2, B2:B500, "Enterprise", C2:C500, "<>Refund")
Where:
D2:D500 is the sum_range (Revenue).
A2:A500, ">="&G1 establishes the start date boundary (e.g., 2026-01-01 ).
A2:A500, "<="&G2 establishes the end date boundary (e.g., 2026-03-31 ).
B2:B500, "Enterprise" isolates the customer tier.
C2:C500, "<>Refund" excludes refunded transactions.
All criteria must evaluate to TRUE for a given row for its revenue value in column D to be included in the sum.
Array Constants for OR Logic Aggregations
Because separate criteria pairs within SUMIFS enforce an AND relationship, attempting to sum across multiple categories (e.g., Department is "Sales" OR "Marketing") cannot be done by simply adding two criteria pairs against the same column.
The Array Constant Technique
To achieve OR logic within a single SUMIFS statement, supply an array constant containing the target criteria values enclosed in curly brackets: =SUMIFS(D2:D500, B2:B500, {"Sales", "Marketing"})
When evaluated, Excel generates an intermediate array containing two distinct sums: {Sales_Total, Marketing_Total} .
To aggregate these sub-totals into a single combined figure, wrap the SUMIFS formula inside the outer SUM function: =SUM(SUMIFS(D2:D500, B2:B500, {"Sales", "Marketing"}, C2:C500, "North"))
This formula sums revenue where Region is "North" and Department is either "Sales" or "Marketing".
Critical Exam Traps & Troubleshooting
Reversing the Calculation Range : Entering sum_range at the end of SUMIFS or at the beginning of SUMIF .
Dimensional Mismatches ( #VALUE! ) : Pairing a calculation range of 100 rows with a criteria range of 99 rows.
Improper Concatenation : Writing ">="F1 (missing & ) or ">=F1" (cell reference inside quotes).
Numeric Wildcard Attempts : Wildcards ( * , ? ) operate exclusively on text data. Writing COUNTIFS(A1:A10, "*5*") will not match the number 152 unless formatted as text.
Division by Zero in AVERAGEIFS : If zero records satisfy all combined criteria, AVERAGEIFS returns #DIV/0! . Wrap the formula in IFERROR to display an alternative value: =IFERROR(AVERAGEIFS(D2:D100, A2:A100, "North"), 0)