Explanation
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 a value with the ISBLANK function :
ISBLANK(C5) // TRUE if empty, FALSE if not
If C5 is empty, ISBLANK returns TRUE and the IF function returns an empty string ("") as the result. If C5 is not empty, ISBLANK returns FALSE and the IF function returns COUNTA function with an expanding reference like this:
COUNTA($C$5:C5) // expanding range
As the formula is copied down, the range expands, and COUNTA returns the “current” count of all non-blank cells in the range as defined in each row. COUNTA will count both numbers and text.
Alternatives
Both of the formulas below perform the same task, but with different syntax:
=IF(C5="","",COUNTA($C$5:C5))
Same logic as above, but using ="" instead of ISBLANK.
=IF(C5<>"",COUNTA($C$5:C5),"")
Logic reversed. If C5 is not blank, return the count, otherwise return an empty string. This version uses the not equal to operator (<>).
Explanation
The ADDRESS function creates a reference based on a given row and column number. In this case, we want to get the first row and the first column used by the named range data (B5:D14). To get the first row used, we use the ROW function together with the MIN function like this:
MIN(ROW(data))
Because data contains more than one row, ROW returns an array of row numbers:
{5;6;7;8;9;10;11;12;13;14}
This array goes directly to the MIN function, which returns the smallest number:
MIN({5;6;7;8;9;10;11;12;13;14}) // returns 5
To get the first column, we use the COLUMN function in the same way:
MIN(COLUMN(data))
Since data contains three rows, COLUMN returns an array with three column numbers:
{2,3,4}
and the MIN function again returns the smallest number:
MIN({2,3,4}) // returns 2
Both results are returned directly to the ADDRESS function, which constructs a reference to the cell at row 5, column 2:
=ADDRESS(5,2) // returns $B$5
If you want a relative address instead of an absolute reference , you can supply 4 for the third argument like this:
=ADDRESS(MIN(ROW(data)),MIN(COLUMN(data)),4) // returns B5
CELL function alternative
Although it’s not obvious, the INDEX function returns a reference, so we can use the CELL function with INDEX to get the address of the first cell in a range like this:
=CELL("address",INDEX(data,1,1))
In this case, we use the INDEX function to get a reference to the first cell in the range by giving INDEX 1 for row number and 1 for column number, with data for array:
INDEX(data,1,1) // returns reference to B5
INDEX then returns a reference to cell B5, and we use the CELL function with “address”, to display the address.
Note: The CELL function is a volatile function that can cause performance problems in large or complex workbooks.
