Formula for Counting Cells in Excel and Sheets

To count cells in a range, use =COUNT(range) to count cells containing numbers, or =COUNTA(range) to count nonempty cells of any type. Enter the range you want to check; the result is a number of cells, and the right formula depends on what should qualify as a count.

Formula

For a rectangular range, the total number of cells is:

Total cells = number of rows × number of columns

• rows is the number of rows in the range.
• cols is the number of columns in the range.
• Total cells is the number of cell positions in the range, including blank cells.

In a spreadsheet, this is a geometric count of the range. It is different from counting cells based on their contents. For example, COUNT counts numeric entries, while COUNTA counts nonempty cells.

Worked Example

For a range with the calculator’s default values of 5 rows and 4 columns:

1. Write the formula: Total cells = rows × columns.
2. Substitute the values: 5 × 4 = 20.
3. The range contains 20 cells, whether those cells are filled or blank.

In Excel or Google Sheets, a direct content-independent formula for a range such as A1:D5 is =ROWS(A1:D5)*COLUMNS(A1:D5). It returns 20.

Choose the Right Spreadsheet Count Formula

Spreadsheet formulas count different things. Choose one according to the cells you want included, rather than assuming that “count” always means the same operation.

• =COUNT(A1:D5) counts cells containing numbers, including dates and times stored as numeric values. Text and empty cells are not counted.
• =COUNTA(A1:D5) counts nonempty cells, including text, numbers, and many formula results. A formula that displays an empty string may still be treated as content by this function.
• =COUNTBLANK(A1:D5) counts blank cells. It can also count cells whose formula returns an empty string, depending on the spreadsheet’s function behavior.
• =COUNTIF(A1:D5,"Yes") counts cells matching one condition, such as the text “Yes.” Criteria can also test numbers or comparisons.
• =COUNTIFS(A1:D5,"Yes",E1:H5,">=10") counts entries that meet multiple conditions in corresponding ranges.

For example, if a four-cell range contains 12, 7, the text “pending,” and one blank, COUNT returns 2, COUNTA returns 3, and COUNTBLANK returns 1. The range itself still has four cells.

Count a Range Without Inspecting Its Contents

To count the positions in a rectangular range, multiply its dimensions. A range from B2 through F8 spans five columns and seven rows, so it contains 5 × 7 = 35 cells. The spreadsheet equivalent is =ROWS(B2:F8)*COLUMNS(B2:F8).

If you need to count cells in a nonrectangular selection, add the sizes of its separate, non-overlapping rectangular parts. Avoid counting overlapping areas twice. For a whole worksheet row or column, spreadsheet functions can report its full grid dimensions, but those dimensions may differ between spreadsheet applications and can change as the grid is expanded. If you only need the number of cells in the data area, use the actual data range instead.

Count Cells That Meet a Condition

Use COUNTIF when one test determines whether a cell qualifies. Its general pattern is =COUNTIF(range, criterion). For instance, =COUNTIF(B2:B100,"Complete") counts cells containing “Complete,” while =COUNTIF(C2:C100,">=50") counts values of 50 or more.

Use COUNTIFS for two or more tests. Each criterion range must correspond in size and shape to the others. For example, counting rows where a status is “Complete” and a score is at least 50 can use =COUNTIFS(B2:B100,"Complete",C2:C100,">=50"). Criteria involving comparison operators are written as quoted criteria; cell references can be joined to operators with &, as in ">="&E1.

Common Counting Problems

• Using COUNT for text: It counts numeric values, not arbitrary filled cells. Use COUNTA for nonempty entries or COUNTIF for a specific condition.
• Confusing blanks with empty-looking cells: A cell may look blank because it contains a formula returning "", spaces, or formatting. Check its contents and test the relevant function on a small range.
• Including a header: If the range includes a heading, COUNTA may count it. Start the range below the header or adjust the expected result.
• Using ranges of different sizes in COUNTIFS: Keep each criterion range aligned and equally sized so criteria are evaluated for corresponding rows.
• Counting visible rows as if they were all rows: Filters hide rows without removing them. A basic count generally includes matching cells in hidden rows; use a method designed for visible-only counting if that is the requirement.

Also distinguish a count of cells from a count of distinct values. Counting every nonempty entry counts duplicates repeatedly. Finding unique values requires a separate function or method, and its availability and name can vary by spreadsheet application.

Frequently Asked Questions

What is the formula to count all cells in a range?
Use =ROWS(A1:D5)*COLUMNS(A1:D5) to count every position in that rectangle, including blanks. Replace the example range with yours.

How do I count cells that are not blank?
Use =COUNTA(A1:A20). It counts cells with content, including text and numbers; check formula-generated empty strings if they matter to your result.

Why does COUNT return zero when the cells contain text?
COUNT counts numeric values, not text. Use COUNTA to count nonempty cells or COUNTIF to count a particular text value.

How do I count blank cells?
Use =COUNTBLANK(A1:A20). If cells appear empty but contain spaces or formulas, inspect them because their contents can affect the result.

How do I count cells that meet two conditions?
Use COUNTIFS, with one range and criterion for each condition. Make sure the ranges have the same dimensions and align row by row.

Related

• Calculation of Pi: Formulas, Methods, and Examples
• Diffusion Models in AI Code Generation: How They Work
• Decimal to Percentage: Formula and Quick Examples

in rows | Number of rows | - | 5 | 0
in cols | Number of columns | - | 4 | 0
out cells | Total cells | cells | rows * cols | 0