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 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...

To use count a non-contiguous range with criteria, you can use the COUNTIF function together with INDIRECT and SUM. In the example shown, cell I5 contains this formula:
=SUM(COUNTIF(INDIRECT({"B5:B8","...

You can reverse a text string with the TEXTJOIN and MID functions, by using an array constant. In the example shown, the formula in C5 is:
=TEXTJOIN("",1,MID(B5,{10,9,8,7,6,5,4,3,2,1},1))
How 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 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 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 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 configure COUNTIFS (or COUNTIF) with a variable range, you can use the OFFSET function. In the example shown, the formula in B11 is:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"<>")
This formula...

To abbreviate text that contains capital letters, you can try this array formula based on the TEXTJOIN function, a new function available in Office 365 and Excel 2019. You can use this approach to create initials from...

To build a formula with a dynamic reference to an Excel Table name, you can use the INDIRECT function with concatenation as needed. In the example shown, the formula in L5 is:
=SUM(INDIRECT(K5&"[Amount]...

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 lookup with a variable sheet name, you can use the VLOOKUP function together with the INDIRECT function.
In the example shown, the formula in C5 is:
=VLOOKUP($B5,INDIRECT("'"&C$4&...

To remove numeric characters from a text string, you can try this experimental formula based on the TEXTJOIN function. In the example shown, the formula in C5 is:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(...

To sum the top values in a range, you can use a formula based on the LARGE function, wrapped inside the SUMPRODUCT function. In the generic form of the formula (above), rng represents a range of cells that contain...