SUMIFS sums only the rows that pass every test. Learn why SUMIF and SUMIFS reverse their arguments (the #1 wrong-total bug), how to compare against a cell with ">"&A1, how AND is built in but OR is not, and how to spill a whole summary table with UNIQUE.
COUNTIFS counts the rows that pass every test, and a wrong criteria gives you a plausible-but-wrong number with no error. Learn why you must write ">"&A1 not ">A1", how AND is built in but OR is not, how to count between two dates, and why distinct counts need UNIQUE, not COUNTIFS.
AVERAGEIFS averages only the rows that pass every test — but when nothing matches it returns #DIV/0! (not 0 like SUMIFS), and it counts zeros while skipping blanks. Learn the empty-set guard, the zero-vs-blank trap, the reversed argument order, and why it can't do a weighted average.
LET lets you declare named variables inside a single formula. Learn the names-in-pairs-result-last rule, why LET computes each value only once (so it is faster, not just tidier), the naming traps, and when a 200-character nested formula should become a LET.
LAMBDA turns a formula into a named, reusable function with no VBA. Learn why a bare LAMBDA returns #CALC!, the trailing-paren trick to call it, how to name it in Name Manager so the whole workbook can use it, and how a LAMBDA can call itself for recursion.
MAP, REDUCE, SCAN, BYROW and BYCOL run a LAMBDA across an array — For Each without a macro. Learn the BYROW single-value rule that fixes #CALC!, the REDUCE accumulator pattern, what shape each helper returns, and when a plain SUMIFS still wins.
VSTACK stacks ranges on top of each other and HSTACK sets them side by side, merging several tables into one array that updates itself. Learn to combine sheets without copy-paste, why ragged ranges pad with #N/A, and how DROP and SORT finish the job.
SEQUENCE conjures an array of numbers out of thin air — it is the engine behind dynamic-array row IDs, date series and grids, not a replacement for the fill handle. Learn the argument order (rows, cols, start, step), how to build a live calendar, why it throws #SPILL!, and when it beats dragging or ROW().
TAKE keeps the first or last N rows or columns of an array; DROP removes them. Together they slice a dynamic array from either edge. Learn the positive/negative trick for top vs bottom, how they pair with SORT for a live Top 10, and how DROP strips headers off a VSTACK.
TEXTJOIN is a glue gun with a delimiter that skips empty cells for you. Learn the ignore_empty argument everyone misses, the 32,767-character limit that throws #VALUE!, when it beats CONCAT and &, and how to join a spilled array back into one cell.
TEXTSPLIT is the formula version of Text to Columns, except the result is a live array that updates with the source. Learn to split into columns and rows at once, pass multiple delimiters as an array constant, fix #SPILL!, and when it replaces LEFT/MID/FIND.
CONCAT replaces CONCATENATE and can take an entire range at once — but it has no delimiter and skips nothing. Learn when CONCAT wins, why CONCATENATE is a legacy leftover, how it reads a 2-D range row by row, the number-format trap, and when to jump straight to TEXTJOIN.
FILTER is a query, not a lookup — it returns every row that matches and spills them into a range. Learn the AND/OR trick, why empty results throw
UNIQUE returns the distinct values from a range and keeps them up to date — unlike the destructive Remove Duplicates button. Learn the exactly_once trap, how to count distinct, and why UNIQUE almost always belongs inside SORT or a dropdown.
The SORT function returns a sorted copy that re-sorts itself when the source changes — unlike the destructive ribbon Sort button. Learn the column-index rule, multi-key sorts, when to reach for SORTBY, and the live SORT(UNIQUE(FILTER())) pattern.
