Counting values in Excel
Number of values in a column
If the number of values in a column is required Excel provides the function '=COUNT()' returning that number. COUNT() also works on dates but returns zero on alphanumeric values:
To count alphanumeric values the following the method is to count the number of empty cells in a given range and to subtract this number from the number of cells in the range. When the range size is specified (e.g. 40000 rows) we get (in a german Excel):
The example above uses the formula '=D3-COUNTBLANK(OFFSET(D6;0;0;D3))' to find the number of non-empty cells in the 40000 cells from D6 down, which works even if empty cells sit between the data cells.
Last row with values
If not the number of values is required but the last row containing a value, Excel does not offer a direct approach. However, knowing the last number may be very convenient for auto-sizing matrix formula ranges to fit the data range, even if the data may contain gaps. In the following we progress to an increasingly compact formulation in terms of file size.
Variant A
From the example above, we want to know the bottom row in column "D" containing the a non-empty value. In a first step, we make use of an auxiliary calculation returning a row numbrer for rows which contain a value in column "D" and 0 otherwise. In cell "E6", the top cell in the auxiliary column, we input '=ROW()*(1-(D6=""))' and drag the formula range down to cell "E15".
The use of the subtraction (1-()) causes conversion of the logical result from the comparison '(D6="")' and corresponds to a 'NOT()' operation. The expression '(1-(D6=""))' returns 0 for empty cells and 1 otherwise. So we can simply multiply by 'ROW()' and the maximum value in column "E" is the required value.
Before showing the variant working without auxiliary column, we reformulate the auxiliary formulae in column "E" as matrix formulae. To keep things simple, we use fixed ranges:
'{=ROW(D6:D15)*(1-(D6:D15=""))}'
Before entering the formula, select the range in which the result should be shown, then enter the formula text (without the curled brackets). and press "Ctrl"+"Shift"+"Enter" to enter. The curled brackets now appear, marking the formula as a matrix formula. As the example shows, the matrix formula in column "G" returns the same values as the scalar formulae in column "E", but differs considerably: 'ROW()' receives an entire range and returns the row number as an element of a matrix. The expression '(D6:D15="")' is also evaluated for every cell in the range and generates a matrix containing TRUE and FALSE, which has the same dimensions as the row number matrix from ROW(D6:D15).
The result of the scalar multiplication is again a matrix which is displayed in the range "G6:G15".
Variant B
For determining the last row in column "D" containing a non-empty value, we are not obliged to show the matrix from the expression '{=ROW(D6:D15)*(1-(D6:D15=""))}'. We can rather insert the expression as the argument to the 'MAX()' functdion, and this result is a single scalar value. So we can enter in a cell of the current example, e.b. "E4", the folloing formula as a matrix formula:
'{=MAX(ROW(OFFSET(D6;0;0;D3))*(1-(OFFSET(D6;0;0;D3)="")))}'
This yields the required row number without the use of an auxiliary column. The expression 'OFFSET(D6;0;0;D3)' is equivalent to the range "D6:D40005", however can easily be adapted to different maximum sizes by changing the value in "D3".
Comments :