which returns 12, the total cells in the range B5:C10.
There is no built-in function for counting the total numbers of cells in a range, so you need to use the ROWS and COLUMNS functions together. In the example, ROWS returns the total number of rows in B5:C10 (6), and COLUMNS returns the total number of columns in B5:C10 (2). The formula multiplies these values together and returns the result:
The ROWS and COLUMNS functions can work with any sized rectangular range. For example, the formula below uses the ROWS function to count all rows in the column A, and the COLUMNS function to count all columns in row 1:
The ROWS function is fully automatic. When you provide a range to ROWS, it will return a count of all rows in the range. In the example, the formula in F5 returns 6, because there are 6 rows in the range B5:C10: = ROWS ( B5:C10 ) // count rows ROWS...
The COLUMNS function is fully automatic. When you provide a range to COLUMNS, it will return a count of all columns in the range. In the example, the formula in F6 returns 2, because there are 2 columns in the range B5:C10: = COLUMNS ( B5:C10 ) //...
The Excel COLUMNS function returns the count of columns in a given reference. For example, COLUMNS(A1:C3) returns 3, since the range A1:C3 contains 3 columns.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.