The heart of this formula is the INDEX function, which is given the list as the array argument:
=INDEX(list
The second part of the formula is an expression that works out the correct row number as the formula is...

The COUNTBLANK function counts the number of cells in the range that don't contain any value and returns this number as the result. Cells that contain text, numbers, dates, errors, etc. are not counted. COUNTBLANK is...

First, a little context. Normally, if you have just a couple things you don't want to count, you can use COUNTIFS like this:
=COUNTIFS(range,"<>apple",range,"<>orange")
But this doesn...

At the core, this formula simply divides tasks complete by the total task count:
=complete/total
which is then formatted as a percentage.
To count complete tasks, we count non-blank cells in the range C5:C11:
=...

The core of this formula is the COUNTA function, configured with an expanding range like this:
COUNTA($B$5:B5)
As the formula is copied down the column, the range starting with B5 expands to include each new row, and...

This formula uses the COUNTA function to count values in a range. COUNTA counts both numbers and text to so works well with mixed data.
The range B4:B8 contains 5 values, so COUNTA returns 5. The number 5 corresponds...

COUNTIF is set up to count cells in the named range "category", which refers to B5:B122. The criteria is supplied as a reference to H4, which simply picks up the value in column F.
the COUNTA function simply counts...

COUNTA is fully automatic. When given a range of cells, it returns a count of cells that contain numbers, text, logical values, and errors. Empty cells are ignored.
With COUNTIF and COUNTIFS
To count non-blank cells...

This page shows an example of a dynamic named range created with the INDEX function together with the COUNTA function. Dynamic named ranges automatically expand and contract when data is added or removed. They are an...

This formula uses the EXACT formula to compare a range of cells to a single value:
=EXACT(B5:F5,B5)
Because we give EXACT a range of values in the first argument, we get back an array result containing TRUE FALSE...

This formula uses the OFFSET function to generate a range that expands and contracts by adjusting height and width based on a count of non-empty cells.
The first argument in OFFSET represents the first cell in the...

At the core, this formula uses the INDEX function to retrieve 10 random names from a named range called "names" which contains 100 names. For example, to retrieve the fifth name from the list, we use INDEX like this...

The COUNTA function counts non-blank cells that contain numbers or text. The first COUNTA counts non-blank cells in the range B5:B11 and returns the number 7:
COUNTA(B5:B11) // returns 7
The second COUNTA function...

At the core, this formula uses the UNIQUE function to extract unique values, and the FILTER function apply criteria.
Working from the inside out, the FILTER function is used to apply criteria and extract only names...

The new dynamic array formulas in Excel 365 make it much easier to solve certain tricky problems with formulas.
In this example, the goal is to generate a list of random 6-character codes. The randomness is handled by...