30 Advanced Excel Tips for 2026

Excel Champs / Excel Formulas List / How to Count the Number of Cells in Excel
By Puneet Gogia (Microsoft MVP)

How to Count the Number of Cells in Excel

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

To count the total number of cells in a range, multiply the rows by the columns: =ROWS(A1:B10)*COLUMNS(A1:B10) returns 20. This counts every cell in the range, whether it holds a value or not. =COUNTA(A1:B10)+COUNTBLANK(A1:B10) gives you the same total by adding the filled cells to the empty ones.

Which one you want depends on what you’re really counting: use =COUNTA(A1:A10) for cells with anything in them, =COUNT(A1:A10) for cells with numbers only, =COUNTBLANK(A1:A10) for empty cells, =COUNTIF(A1:A10,"*") for cells with text, and =COUNTIF(A1:A10,"*John*") for cells containing specific text anywhere inside.

One catch worth knowing: a cell holding a formula that returns “” looks empty but is counted by COUNTA and ignored by COUNTBLANK, so the two won’t always add up to the range size on a sheet full of formulas.

Let’s say you work in HR and need to track employee data in a 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. Let’s say 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 COLUMNS function by referring to the same range (COLUMNS(A1:B10)).
  4. In the end, press Enter to get the result in the cell.
=ROWS(A1:B10)*COLUMNS(A1:B10)
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

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.

This method doesn’t look at what’s in the cells at all, so it always gives the exact number of cells in the range. If your data is in an Excel table, you can refer to the table name instead, and the count updates as the table grows: =ROWS(Table1)*COLUMNS(Table1).

COUNTA & COUNTBLANK to Count Cells from a Range

The second method needs you to combine the COUNTA and COUNTBLANK functions. By combining both functions, you can count all the non-blank cells and all the blank cells in 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.

But there’s one catch: a cell with a formula that returns a blank (“”) is counted by both COUNTA and COUNTBLANK, so it gets counted twice, and the total comes out higher than the real number of cells. If your range can have such formulas, use the ROWS and COLUMNS method instead.

Read Also – Count Colored Cells in Excel

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 (Excel 2021, Excel 2024 and Microsoft 365). 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.

If the range has blank cells, UNIQUE returns the blank as one more value, and COUNTA counts it. To leave blanks out, filter them first:

=COUNTA(UNIQUE(FILTER(A1:A10,A1:A10<>"")))

And in older versions without UNIQUE, you can use this formula, which also skips blank cells:

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

For more methods, see Count Unique Values in Excel.

Count Cells with Text

To count the number of cells with text in a range, you can use 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 (numbers, dates and empty cells are not counted). 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 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. The match isn’t case-sensitive, so “john” and “JOHN” are counted too.

Count Cells with a Partial Text

You can count cells containing partial text using 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”. So, it also counts cells like “Johnny”.

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. Dates are numbers in Excel, so COUNT counts them too, but numbers stored as text are not counted.

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, including cells with a space or a formula that returns “”.

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. It also counts cells with a formula that returns “”, but not cells with a space or a zero.

Count Cells 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 cells with a color.

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 in the range A1:A10 and you want to count cells with the same fill color as cell B1, write the formula like the following:

=CountColorCells(A1:A10, B1)

Keep two things in mind with this function. It reads the fill color you have applied, not colors from conditional formatting. And changing a color doesn’t trigger a recalculation, so press F9 (or Ctrl + Alt + F9) to update the result after you change colors. Save the file as a macro-enabled workbook (.xlsm) to keep the function.

Frequently Asked Questions

How do I count cells without a formula?

Select the range and look at the status bar at the bottom of Excel. The Count shows the number of non-empty cells; right-click the status bar and turn on Numerical Count to see the count of cells with numbers.

Why is COUNTA plus COUNTBLANK more than the number of cells?

Because a formula that returns “” is counted by both functions. ROWS multiplied by COLUMNS always returns the exact number of cells.

How do I count cells in more than one range?

Add the counts of each range, like =ROWS(A1:B10)*COLUMNS(A1:B10)+ROWS(D1:D5)*COLUMNS(D1:D5). COUNTA and COUNT also accept more than one range separated by commas (COUNTBLANK takes only one range).

Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel

Leave a Comment