The functions I use most often
COLUMN/COLUMNS and ROW/ROWS
Function type: range/cell position and size.
Use these functions within formulas when you need to use the position of a cell or range as an input to your calculations. See more details here
COUNTIF/COUNTIFS
Function type: Summing and counting
Use these functions to conditionally count cells in a range, for example, when it is necessary to work out the fraction of records that fall within a certain category. See full explanation here
EOMONTH
Function type: Date and Time functions
Use this function to generate an Excel date that is the last day of the given month. The reason this function is so powerful is that you can specify a month offset, positive or negative, of the number of months different that you want to generate the date for. Read the full description of EOMONTH
INDEX
Function type: point to a cell/range.
This function returns the reference within a specified block, as defined by the row and column entered into the function. This is the function I use every day ... I fetch values from blocks of data using INDEX in conjunction with the MATCH function. Read the full description of INDEX
INDIRECT
Function type: cell/range reference.
Create a reference to a cell/range indirectly ... that is, use a string representation of the required address, and this function converts it into a reference that can be used. See example here
MATCH
Function type: lookup function.
This function returns the position of an item in a list. This can then be combined with other function to fetch data, or generate an area to sum, or anything else where a position of an item is required. See more information here.
OFFSET
Function type: point to a cell/range.
Create a reference to a cell/range, offsetting from a starting cell by the required number of rows and columns (and returning a range size of a specified number of rows and columns .... a range). See more information here.
ROW/ROWS and COLUMN/COLUMNS
Function type: Summing and counting
Use these functions to conditionally count cells in a range, for example, when it is necessary to work out the fraction of records that fall within a certain category. See more details here.
SUBTOTAL/AGGREGATE
Sum of range, but exclude filtered or hidden rows. In lists containing multiple levels of subtotalling, this function ignores other instances of SUBTOTAL, and so prevents double-counting. The AGGREGATE function does the same, but has powerful additional features to exclude error cells. No details exist for these functions yet.
SUMIF/SUMIFS
Function type: Summing and counting
Use these functions to conditionally count cells in a range, for example, when it is necessary to work out the fraction of records that fall within a certain category. No details exist for these functions yet.
VLOOKUP
Function type: lookup function.
Look up a value in a vertical list, fetching an item that corresponds to a specific value in the left-most column of the list. Full details in this link to a page for VLOOKUP