To count rows that contain specific values, you can use an array formula based on the MMULT, TRANSPOSE, COLUMN, and SUM functions. In the example shown, the formula in G5 is:
{=SUM(--(MMULT(--(data=90),TRANSPOSE(...

To copy values or generate references with a pattern like every 3rd column, every 5th column, etc. you can use a formula based on the the OFFSET and COLUMN functions. In the example shown, the formula in C8 is:
=...

To sum every nth column, you can use a formula based on the SUMPRODUCT, MOD, and COLUMN functions.
In the example shown, the formula in L5 is:
=SUMPRODUCT(--(MOD(COLUMN(B5:J5)-COLUMN(B5)+1,K5)=0),B5:J5)
How this...

To get the full address of a named range with an Excel formula, you can use the ADDRESS function together with the ROW and COLUMN functions. In the example shown, the formula in G5 is:
=ADDRESS(ROW(data),COLUMN(data),...

To count rows that contain specific values, you can use an array formula based on the MMULT, TRANSPOSE, COLUMN, and SUM functions. In the example shown, the formula in K6 is:
{=SUM(--(MMULT(--((data)<70),TRANSPOSE(...

To get the address of the first cell in a named range, you can use the ADDRESS function together with the ROW and COLUMN functions.
In the example shown, the formula in G5 is:
=ADDRESS(ROW(data),COLUMN(data))
How...

To sum the last n columns in a table of data (i.e. last 3 columns, last 4 columns, etc.) you can use a formula based on the INDEX function. In the example shown, the formula in K5:
=SUM(INDEX(data,0,COLUMNS(data)-(K4-...

To count with repeating numbers (for example: 1111,2222,3333,4444, etc.) you can use a formula based on the ROUNDUP function, with help from either ROW or COLUMN.
In the example shown, the formula in C4 is:
=ROUNDUP...

To get a full set of relative column numbers in a range, you can use an array formula based on the COLUMN function.
In the example shown, the array formula in B4:H4 is:
{=COLUMN(B4:H4)-COLUMN(B4)+1}
On the worksheet...

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

If you need to dynamically increment a calculation, so that a value automatically increments each time the formula is copied to a new row or column, you can use the ROW() or COLUMN() functions in your formula.
In the...

To write a formula that will sum "the next 3" cells each time it's copied, you can use the OFFSET function. In the example shown, the formula in O5 is:
=SUM(OFFSET($B5,0,(COLUMN()-COLUMN($O$5))*3, 1,3))
Note: the...

To locate the position of a value in a 2D array, you can use the SUMPRODUCT function. In the example shown, the formulas used to locate the row and column numbers of the max value in the array are:
=SUMPRODUCT((data=...

To get the max of every nth column, you can use an array formula based on the MAX, MOD, and COLUMN functions. In the example shown, the formula in M5 is:
=MAX(IF(MOD(COLUMN(B5:K5)-COLUMN(B5)+1,L5)=0,B5:K5))
Note:...

To lookup a value by matching across multiple columns, you can use an array formula based on the MMULT, TRANSPOSE, COLUMN, and INDEX. In the example shown, the formula in H4 is:
{=INDEX(groups,MATCH(1,MMULT(--(names=...