Creating Advanced Formulas
Argument order is the whole game
The advanced-formula domain rewards precise recall of signatures. Most missed questions are not conceptual - they are about which range goes first.
Conditional aggregation. SUMIFS(sum_range, criteria_range1, criteria1, ...), COUNTIFS(criteria_range1, criteria1, ...), and AVERAGEIFS(average_range, criteria_range1, criteria1, ...). Note that SUMIFS and AVERAGEIFS put the aggregate range first, while the older single-condition SUMIF(range, criteria, sum_range) puts it last. Criteria strings use operators and wildcards: ">=100", "<>West", "A*".
Lookups. XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) needs no column counting and defaults to exact match. INDEX(array, row_num, [col_num]) combined with MATCH(lookup_value, lookup_array, 0) is still tested.
Dynamic arrays and modern functions. FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, and RANDARRAY spill automatically; the spilled range is referenced with the # operator. LET names intermediate results to avoid recalculation; LAMBDA creates reusable custom functions. IFERROR and IFNA handle errors, IFS and SWITCH replace nested IFs.
Volatility and errors. OFFSET and INDIRECT are volatile and recalculate on every change. Watch for #SPILL! (blocked spill area), #N/A (no match) and #VALUE! (wrong argument type).
Rule of thumb: say the function name out loud as its first argument - "SUM the sum range IFS these criteria" - and you will almost never invert SUMIFS.
Worked check: =SUMIFS(Sales[Amount], Sales[Region], "West", Sales[Amount], ">500") sums Amount where Region is West and Amount exceeds 500.