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

In the example shown, the goal is to add row numbers in column B only when there is a value in column C. The formula in B5 is:
=IF(ISBLANK(C5),"",COUNTA($C$5:C5))
The IF function first checks if cell C5 has...

The SORTBY function allows sorting based on one or more "sort by" arrays, as long long as they have dimensions that are compatible with the data being sorted. In this example, there are 10 values being sorted, the...

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

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 named range "key" (C4:G4) for convenience only. Without the named range, you'll want to use an absolute reference so the formula can be copied.
In cell I7, we have this formula:
=SUM(--(C7:G7=...

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

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

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

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

In this example, the goal is to calculate a count and percentage for each category shown in column B. For convenience, the category values in column B are in the named range category (B5:B122). To generate the count, we...

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