What are the most useful Excel functions for analysis and how do you use them?

Conditional aggregation — the workhorses:

• SUMIFS, COUNTIFS, AVERAGEIFS — aggregate with multiple criteria. These replace most manual filtering.

Lookup:

• XLOOKUP or INDEX with MATCH for retrieving related values.

Logic:

• IF, and IFS for multiple conditions without nesting six levels deep.
• AND, OR, NOT to combine tests.
• IFERROR to handle failures gracefully — but use it deliberately, since it hides genuine problems as well as expected ones.

Text:

• TEXTJOIN, LEFT, RIGHT, MID, LEN, SUBSTITUTE, TRIM.

Date:

• EOMONTH, EDATE, DATEDIF, NETWORKDAYS, YEAR, MONTH.

Dynamic arrays, which changed how modern Excel is written:

• FILTER — returns rows matching a condition, spilling into a range. This alone replaces a great deal of manual work.
• UNIQUE, SORT, SEQUENCE.
• LET to name intermediate calculations inside a formula, which makes long formulas readable and faster.
• LAMBDA to define reusable custom functions without VBA.

Note: Naming FILTER, LET, and LAMBDA signals you have kept up. Many candidates are still describing Excel 2010.