To use COUNTIFS with a variable table column, you can use INDEX and MATCH to find and retrieve the column for COUNTIFS. In the example shown, the formula in H5 is:
=COUNTIFS(INDEX(Table1,0,MATCH(G5,Table1[#Headers],0...

To create a formula with a dynamic sheet name you can use the INDIRECT function.
In the example shown, the formula in C6 is:
=INDIRECT(B6&"!A1")
Note: The point of this approach is it lets you to build...

If you need to sum or add the bottom values in a range, you can do so with a formula that uses the SMALL function wrapped inside the SUMPRODUCT function. In the generic form of the formula (above), rng represents a...

To calculate work hours between two dates with a custom schedule, you can use a formula based on the WEEKDAY and SUMPRODUCT functions, with help from ROW, INDIRECT, and MID. In the example shown, the formula in F8 is...

To build a dynamic worksheet reference - a reference to another workbook that is created with a formula based on variables that may change - you can use a formula based on the INDIRECT function.
In the example shown,...

To use data validation to allow a list of specific characters only, you can use a rather complicated array formula based on the COUNT, MATCH, and LEN functions. In the example shown, data validation is applied with this...

To create an array of numbers like {1;2;3;4;5} you can use a formula based on the ROW and INDIRECT functions. This technique is most often used in array formulas that need a numeric array for processing of some kind. In...

To convert a string to an array that contains one item for each letter, you can use an array formula based on the MID, ROW, LEN and INDIRECT functions. This can sometimes be useful inside other formulas that manipulate...

To create a formula with a "locked" reference – a reference that won't be adjusted during copy or paste, or when rows and columns are changed in a worksheet – you can use the INDIRECT function.
How this formula works...

To allow a dynamic lookup table, you can use the INDIRECT function with named ranges inside of VLOOKUP. In the example shown the formula in G5 is:
=VLOOKUP(F5,INDIRECT(E5),2,0)
Background
The purpose of this formula...

To test if a worksheet name exists in a workbook, you can use a formula based on the ISREF and INDIRECT functions. In the example shown, the formula in C5 is:
=ISREF(INDIRECT(B5&"!A1"))
How this formula...

To increment a reference created as text inside the INDIRECT function, you can use the CELL function. In the example shown, the formula in D5 is:
=INDIRECT($B$5&"!"&CELL("address",A1))...

To get cell content with a given row and column number, you can use the ADDRESS function together with INDIRECT. In the example shown, the formula in G6 is:
=INDIRECT(ADDRESS(G4,G5))
How this formula works
The Excel...

To count the number of Mondays, Fridays, Sundays, etc. between two dates you can use an array formula that uses several functions: SUMPRODUCT, WEEKDAY, ROW, and INDIRECT.
In the example shown, the formula in cell E6 is...

To convert a column letter to an regular number (e.g. 1, 10, 26, etc.) you can use a formula based on the INDIRECT and COLUMN functions.
In the example shown, the formula in C5 is:
=COLUMN(INDIRECT(B5&"1...