Excel Functions

Excel COUNTIF Function

COUNTIF counts cells that match a single condition — no formulas, no helper columns. Here's how the syntax works and where people get it wrong.

Excel COUNTIFS Function

COUNTIFS extends COUNTIF to handle multiple conditions at once, no helper columns required. Here's the syntax, real examples, and where people get tripped up.

Excel FILTER Function

FILTER pulls matching records out of a data set without formulas, helper columns, or the Data tab. Change the source data and the results update on their own.

Excel IF Function

IF is the function behind almost every conditional formula in Excel — pass or fail, over or under budget, yes or no. Here's how to write one that works on the first try.

Excel IFERROR Function

IFERROR replaces ugly error codes like #N/A and #DIV/0! with a value you choose. It's simple to use, but it catches every error type — including the ones you didn't mean to hide.

Excel INDEX Function

INDEX is the backbone of Excel's most reliable lookup pattern, INDEX/MATCH. Here's how the array form, reference form, and area_num argument actually work — and where INDEX breaks in ways VLOOKUP never will.

Excel INDIRECT Function

INDIRECT turns plain text into a working cell reference, which means you can build formulas that point at different sheets, ranges, or workbooks based on what's typed into a cell. It's also volatile and easy to misuse — here's how to use it without wrecking your workbook's performance.

Excel MATCH Function

MATCH doesn't return data — it returns a row or column number, which is exactly why INDEX needs it. Here's how the syntax works and where the default match type quietly breaks formulas.

Excel OFFSET Function

OFFSET builds a reference on the fly instead of pointing at a fixed cell. That flexibility makes it useful for dynamic ranges and rolling calculations — and it's also why it can quietly slow down a large workbook.

Excel RAND and RANDBETWEEN Functions

Need random numbers for test data or simulations? RAND and RANDBETWEEN generate them instantly. Here's when to use each one.

Excel SUBTOTAL Function

SUBTOTAL is the one Excel function built to skip filtered rows automatically. Here's how the function_num codes work, how the Data > Subtotal feature uses them, and the formulas for summing only what's visible.

Excel SUMIF Function

SUMIF adds up values in a range that meet one condition you specify. This guide covers the syntax, real formulas, and the mistakes that trip up beginners.

Excel SUMIFS Function

SUMIFS adds up values that meet every condition you set — region, date, product, all at once. Here's the syntax, the argument-order trap everyone hits, and real formulas you can copy.

Excel SUMPRODUCT Function

SUMPRODUCT multiplies and sums arrays in a single step, which makes it the go-to formula for multi-criteria sums, OR logic, and weighted averages that SUMIFS can't touch. It also works on closed workbooks and needs no array-entry shortcut.

Excel VLOOKUP Function

VLOOKUP searches the first column of a table for a value, then returns data from any column to the right. Widely used, easy to misconfigure — especially around its default match mode.

Excel XLOOKUP Function

XLOOKUP replaces VLOOKUP and HLOOKUP with a single function that looks in any direction and stays intact when columns are inserted. Here's the syntax, the gotchas, and when to still reach for VLOOKUP instead.

INDEX and MATCH in Excel: Two-Dimensional Lookups

VLOOKUP and HLOOKUP each search in one direction. Combine INDEX and MATCH and you can look up any value at the intersection of a row and column — no column number hardcoding required.

VLOOKUP and HLOOKUP Functions