When you work with a worksheet, you may wonder how many cells are in a range, both with data and blank ones. There are different methods you can figure out this question and we will discuss two of them in this article.
Example: You are working with a dataset with the first name in column A, the last name in column B, and the salary in column C. Assume you are curious about how many cells are in the highlighted range.
Formula 1: To count the number of cells that are blank and those that are not blank
=SUM(COUNTBLANK(A1:D9), COUNTA(A1:D9))
- COUNTBLANK(A1:D9): To count the number of blank cells in the range, the result returns 18;
- COUNTA(A1:D9): To count the number of cells that are not blank, the result returns 18;
- SUM(COUNTBLANK(A1:D9), COUNTA(A1:D9)): To add both blank and not blank cells.
The result returns 36, and the range has 36 cells.
Formula 2: To count the number of cells by rows and columns
= ROWS(A1:D9)*COLUMNS(A1:D9)
- ROWS(A1:D9): To count the number of rows in the range, the result returns 9;
- COLUMNS(A1:D9): To count the number of columns in the range, the result returns 4;
- ROWS(A1:D9)*COLUMNS(A1:D9): The product of the number of rows and columns.
The result returns 36, and the range has 36 cells.
Notes: The ROWS function
Formula:
= ROWS(array)
Explanations:
The array is required. The number of rows in the data ranges you are looking for.