How to Count the Number of Cells in Excel

- Written by Puneet

excelchamps-free-courses-puneet-gogia

Let’s say you work in HR and need to track employee data in an workbook. You have a table with columns for names, ages, departments, and start dates. You want to know the total number of cells in this table to ensure all data is complete and organized.

Count Number of Cells in Excel

To find the number of cells, you can use the ROWS and COLUMNS functions. If your table ranges from A1 to B10.

  • ROWS counts the number of rows in a given range. For example, =ROWS(A1:A10) returns ten because there are ten rows in the range A1 to A10.
  • COLUMNS counts the number of columns in a given range. For example, =COLUMNS(A1:C1) returns three because there are three columns in the range A1 to C1.

In the following example, we have a few values in column A, and you need to count the cells from the range A1:B10. And for this, we will use the ROWS and COLUMNS functions. Steps to write the formula:

  1. First of all, in cell D1, enter “=ROWS (A1:B10)“.
  2. Next, type the asterisk sign (*) to multiply the values.
  3. After that, you need to enter the COLUMN function by referring to the same range (COLUMNS(A1:B10).
  4. In the end, press enter button to the result in the cell.
rows-and-columns-function

Now as you can see, when you hit enter, it returns 20 in the result as you have a total of 20 cells in the range you have specified.

cells-count-result

Now let’s try to understand the formula that we have used here. In the first part, we have used the ROWS function which returns the number of rows that you have in a range. Here it returns 10 as you have ten rows in the range A1 to B10.

rows-formula

Now when next you have the COLUMNS function that returns the number of columns you have in a range. Here it returns 2 as you have two columns in the range A1 to B10.

columns-formula

In the end, you have an asterisk sign between both the values to multiply rows and columns to get the total number of cells in the range.

COUNTA & COUNTBLANK to Count Cells from a Range

The second method wants you to combine COUNTA and COUNTBLANK functions. By combining both functions you can count all the non-blank cells and all the blank cells from a range.

Below we have the same example that we used in the first example. In cell D1, you can enter the following formula:

=COUNTA(A1:B10)+COUNTBLANK(A1:B10)
counta-countblank-formula

As I said, this formula works in two parts:

  1. COUNTA function counts the cells where you have values.
  2. COUNTBLANK function counts the cells where you don’t have values.

In the end, you have a “+ sign” which sums both numbers and returns you the total count of cells that you have in the range that you have specified.

Count Number of Cells in Excel Excluding Duplicates

To count the number of unique cells in a range, excluding duplicates (unique values in a range), you can use the COUNTA function combined with the UNIQUE function. Suppose your data is in the range A1:A10. You can use the below formula:

=COUNTA(UNIQUE(A1:A10))
  • UNIQUE(A1:A10) finds all unique names in the range A1 to A10.
  • COUNTA then counts how many unique names there are.

This way, you get the total number of cells with the names, ignoring duplicates.

Count Cells with Text

To count the number of cells with text in a range, you can use the COUNTIF. Suppose your data is in the range A1:A10. Use this formula:

=COUNTIF(A1:A10, "*")

In this formula, COUNTIF(A1:A10, “*”) counts all cells that contain text in the range A1:A10. And the * wildcard represents any text.

Count Cells with a Specific Text

To count the number of cells containing specific text in a range, use the COUNTIF. Let’s say you want to count cells with the text “John” in the range A1:A10. Use the below formula:

=COUNTIF(A1:A10, "John")

It counts all cells that exactly match the text “John” in the range A1 to A10.

Count Cells with a Partial Text

You can count cells containing partial text using the COUNTIF with wildcards. Let’s say you want to count cells containing the text “John” anywhere in the range A1:A10.

=COUNTIF(A1:A10, "*John*")

In this formula, COUNTIF(A1:A10, “John“) counts all cells that have the text “John” anywhere within the cell in the range A1:A10. The asterisk * is a wildcard character representing any sequence of characters before or after “John”.

Count Cells with Numbers

You can use the function COUNT to count the number of cells that contain numbers in a range. Suppose your data is in the range A1:A10.

=COUNT(A1:A10)

It counts all cells that contain numbers in the range A1 to A10.

Count Non-Blank Cells

To count non-blank cells (non-empty cells) in a range, you can use the COUNTA function. Let’s say your data is in the range A1:A10.

=COUNTA(A1:A10)

It counts all cells that are not empty in the range A1 to A10.

Count Blank Cells

You can use the COUNTBLANK function to count the number of blank cells in a range. Let’s say you have data in the range A1:A10.

=COUNTBLANK(A1:A10)

It counts all empty cells in the range A1 to A10.

Count Cell with a Specific Color

There is no built-in function to count cells by color. The best way to create a formula for this is to write a code to create a custom function that counts the cell with colors.

Function CountColorCells(rng As Range, color As Range) As Long
    Dim cell As Range
    Dim count As Long
    count = 0
    
    For Each cell In rng
        If cell.Interior.Color = color.Interior.Color Then
            count = count + 1
        End If
    Next cell
    
    CountColorCells = count
End Function

To add this function, press Alt + F11 to open the VBA editor, and then insert a new module by clicking Insert > Module. Paste the code into the module.

If you have values is in range A1:A10, and you want to count cells with the same color as cell B1. Write the formula like following:

=CountColorCells(A1:A10, B1)
Last Updated: May 19, 2024

Leave a Comment