Explanation

The goal is to count the number of cells in a given range, regardless of whether the cells are empty or not. Although Excel has several functions designed to count cells based on their contents, there is no built-in function for counting the total number of cells in a range. The classic solution is to use a formula based on the ROWS function and the COLUMNS function. It is also possible to force the COUNTA function to count all cells, empty or not. Both approaches are explained below.

ROWS + COLUMNS

One way to count all cells in a range is to use the ROWS function with the COLUMNS function in a formula like this:

=ROWS(range)*COLUMNS(range)

This is the approach used in the worksheet shown, where the formula in cell J5 is:

=ROWS(B4:H16)*COLUMNS(B4:H16)

The ROWS function returns the count of rows in a range. Since there are 12 rows in the range B4:H16, ROWS returns 12. In the same way, the COLUMNS function returns the count of columns in a range, which is 7 in this case. The formula is evaluated by Excel like this:

=ROWS(B4:H16)*COLUMNS(B4:H16)
=12*7
=91

Count all cells in a worksheet

The ROWS and COLUMNS functions can work with a rectangular range of any size. For example, the formula below uses the ROWS function to count all rows in column A, and the COLUMNS function to count all columns in row 1:

=ROWS(A:A)*COLUMNS(1:1) // count all cells in worksheet

ROWS returns a count of 1,048,576, and COLUMNS returns a count of 16,384, so the final result is 17,179,869,184. This is the total number of cells in an Excel worksheet. The formula below uses the range 1:1048576 to achieve the same result:

=ROWS(1:1048576)*COLUMNS(1:1048576) // returns 17179869184

Tip: Excel will enter the range 1:1048576 for you if you click the upper-left corner of the spreadsheet while entering a formula.

COUNTA alternative

If you find the ROWS + COLUMNS formula cumbersome, you can try the formula below, which is based on the COUNTA function:

=COUNTA(range+0)

This formula is a bit of a hack. The COUNTA function will count cells that contain numbers, text, or errors. It will not count empty cells. If we use COUNTA with the range itself, we get a result of 82, since the range contains 9 empty cells:

=COUNTA(B4:H16) // returns 82

However, when we add zero:

=COUNTA(B4:H16+0)

The match operation will cause Excel to evaluate the empty cells as zeros, and it will return a combination of numbers and errors. In this example, the array created inside COUNTA looks like this:

=COUNTA({0,18,46,54,#VALUE!,23,13;10,#VALUE!,76,#VALUE!,64,14,64;44,34,0,39,30,0,#VALUE!;81,90,#VALUE!,58,#VALUE!,#VALUE!,75;32,79,56,14,1,9,70;79,#VALUE!,8,0,92,64,#VALUE!;#VALUE!,56,89,18,42,43,44;48,38,#VALUE!,68,#VALUE!,0,91;32,0,59,#VALUE!,1,#VALUE!,#VALUE!;41,#VALUE!,39,55,72,0,57;61,68,#VALUE!,#VALUE!,18,87,#VALUE!;80,65,48,96,5,36,0;11,92,#VALUE!,36,#VALUE!,0,65})

Notice that empty cells are now zero (0), and errors represent cells that contain text. Because all cells represented by the array have a value, COUNTA will now return the same result as the original formula above, 91.

Caution: this formula will work fine on fine on typical size ranges. However, the performance will suffer on very large ranges, and you might notice a delay when the formula is entered or recalculated.

Explanation

There is no direct way to detect a hidden column with a formula in Excel. You might think of using the SUBTOTAL function , but SUBTOTAL only works with vertical ranges. As a result, the approach described in this example is a workaround based on a helper formula that must be entered in a range that includes all columns in the scope of interest. In this example, this range is the named range “key”.

In the example shown, columns C and E are hidden. The helper formula, entered in B4 and copied across B4:F4, is based on the CELL function :

=CELL("width",B4)>0

The CELL function will only return a width for a cell in a visible column. When a column is hidden, the same formula will return zero. By checking if the result is greater than zero, we get a TRUE or FALSE result. The N function is used to coerce TRUE to 1 and FALSE to zero, so the final result is 1 when a column is visible, and 0 when a column is hidden. Nice.

To count visible columns, we use the SUM function formula in I4:

=SUM(key)

where “key” is the named range B4:F4.

Count hidden columns

To count hidden columns, the formula in I5 is:

=COLUMNS(key)-SUM(key)

The COLUMNS function returns the total columns in the range (5) and the SUM function returns the sum of visible columns (3), so the final result is 2:

=COLUMNS(key)-SUM(key)
=5-3
=2

With other operations

Once you have the “column key” in place, you can use it with other operations. For example, you could SUM values in visible columns by using SUM like this:

=SUM(key*B6:F6)

Although each cell in B6:F6 contains the number 25, SUM will return 75 when column C and E are hidden, as shown in the example.

Note: CELL function is a volatile function . Volatile functions normally recalculate with every worksheet change, so they can cause performance problems. Unfortunately, CELL does not fire when a column is hidden or made visible again. This means you will not see correct results until the worksheet recalculates, either with a normal change, or by pressing F9.