Back to Blog

How to Count Cells in Google Sheets: Complete Guide with Examples | SheetXAI

D
David DeSouza
Dec 1, 2025
Illustration for How to Count Cells in Google Sheets

The Problem

You need to count cells in your Google Sheets data. This could mean counting all cells with data, counting only numeric cells, counting blank cells, or counting cells that meet specific criteria. Different situations require different counting functions.

The Easy Way: Use SheetXAI

If you don't want to mess with formulas, the fastest way to count cells is simply by asking SheetXAI.

With SheetXAI, you can open the sidebar and type:

Count how many cells have data in column A.

Or be more specific:

Count how many cells in column B contain numbers greater than 100.

SheetXAI will instantly generate the right formula and count the cells for you. It handles all the syntax so you can focus on getting the answer.

The Manual Way: The Formulas You Need

To count cells manually in Google Sheets, you need to use different functions depending on what you want to count:

1. COUNT Function

What it does: Counts only cells that contain numbers.

Syntax: =COUNT(value1, [value2], ...)

Parameters:

  • value1: The first cell or range to count
  • [value2]: Additional cells or ranges (optional)

Example:

=COUNT(A1:A100)

This counts how many cells in the range A1:A100 contain numbers.

Use case: When you want to count only numeric values, ignoring text and blank cells.

2. COUNTA Function

What it does: Counts all non-empty cells (text, numbers, formulas that return values).

Syntax: =COUNTA(value1, [value2], ...)

Parameters:

  • value1: The first cell or range to count
  • [value2]: Additional cells or ranges (optional)

Example:

=COUNTA(A1:A100)

This counts all non-empty cells in the range A1:A100, including text, numbers, and formulas.

Use case: When you want to count all cells with any data, regardless of type.

3. COUNTBLANK Function

What it does: Counts cells that are empty.

Syntax: =COUNTBLANK(range)

Parameters:

  • range: The range of cells to check for blanks

Example:

=COUNTBLANK(A1:A100)

This counts how many cells in the range A1:A100 are empty.

Use case: When you want to know how many blank cells you have in a range.

4. COUNTIF Function

What it does: Counts cells that meet a specific condition.

Syntax: =COUNTIF(range, criteria)

Parameters:

  • range: The range of cells to evaluate
  • criteria: The condition that must be met (e.g., ">100", "Complete", "Text")

Example:

=COUNTIF(A1:A100, ">100")

This counts cells in A1:A100 where the value is greater than 100.

Use case: When you want to count cells based on specific criteria.

5. COUNTIFS Function (Multiple Criteria)

What it does: Counts cells that meet multiple conditions.

Syntax: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Parameters:

  • criteria_range1: The first range to evaluate
  • criteria1: The first condition
  • [criteria_range2, criteria2]: Additional range-condition pairs (optional)

Example:

=COUNTIFS(A1:A100, ">100", B1:B100, "Active")

This counts rows where column A is greater than 100 AND column B equals "Active".

Use case: When you need to count cells based on multiple criteria.

Understanding the Logic

The key to counting cells correctly is understanding what each function counts:

  1. COUNT: Only numeric values (numbers, dates, times)
  2. COUNTA: All non-empty cells (text, numbers, formulas, anything except truly empty)
  3. COUNTBLANK: Only empty cells
  4. COUNTIF: Cells meeting one condition
  5. COUNTIFS: Cells meeting multiple conditions

Important relationships:

  • COUNT(range) + COUNTBLANK(range) + text cells = total cells
  • COUNTA(range) + COUNTBLANK(range) = total cells in range
  • COUNT(range) ≤ COUNTA(range) (COUNT is a subset of COUNTA)

Common Formula Examples:

// Count all cells with data
=COUNTA(A1:A100)

// Count only numeric cells
=COUNT(A1:A100)

// Count blank cells
=COUNTBLANK(A1:A100)

// Count cells greater than 100
=COUNTIF(A1:A100, ">100")

// Count cells with text "Complete"
=COUNTIF(B1:B100, "Complete")

// Count cells meeting multiple conditions
=COUNTIFS(A1:A100, ">100", B1:B100, "Active")

Common Use Cases

Count All Cells with Data

To count all cells that contain any data:

=COUNTA(A1:A100)

This is the most common counting need - finding out how many cells have data.

Count Only Numeric Cells

To count only cells containing numbers:

=COUNT(A1:A100)

This ignores text cells and blank cells, counting only numbers.

Count Blank Cells

To count how many cells are empty:

=COUNTBLANK(A1:A100)

Useful for data quality checks and identifying missing data.

Count Cells with Specific Text

To count cells containing specific text:

// Exact match
=COUNTIF(A1:A100, "Complete")

// Contains text (partial match)
=COUNTIF(A1:A100, "*Complete*")

// Starts with text
=COUNTIF(A1:A100, "Complete*")

// Ends with text
=COUNTIF(A1:A100, "*Complete")

Count Cells with Numbers Greater/Less Than

To count cells with numeric comparisons:

// Greater than
=COUNTIF(A1:A100, ">100")

// Less than
=COUNTIF(A1:A100, "<100")

// Equal to
=COUNTIF(A1:A100, "=100")

// Not equal to
=COUNTIF(A1:A100, "<>100")

// Between two values
=COUNTIFS(A1:A100, ">=10", A1:A100, "<=20")

Count Cells Across Multiple Columns

To count cells with data across multiple columns:

=COUNTA(A:A) + COUNTA(B:B) + COUNTA(C:C)

Or count cells in a rectangular range:

=COUNTA(A1:C100)

Count Unique Cells

To count how many unique values you have:

=COUNTA(UNIQUE(A1:A100))

This counts the number of unique values in the range.

Advanced Techniques

Count Cells with Formulas That Return Empty Strings

Sometimes cells contain formulas like =IF(A1="", "", "Value") that return empty strings. These are counted by COUNTA but appear blank.

To count only truly non-empty cells (excluding empty strings):

=SUMPRODUCT((A1:A100<>"")*1)

Count Cells Based on Cell Color (Requires Apps Script)

Google Sheets doesn't have a built-in function to count by color. You would need Apps Script or use SheetXAI to handle this.

Count Cells with Dates

To count cells containing dates:

=COUNT(A1:A100)

COUNT works for dates because dates are stored as numbers in Google Sheets.

Or count dates in a specific range:

=COUNTIFS(A1:A100, ">="&DATE(2024,1,1), A1:A100, "<="&DATE(2024,12,31))

Count Non-Blank Cells Excluding Header

If your first row is a header and you want to count data cells only:

=COUNTA(A2:A100)

Or if you want to exclude the header from a full column:

=COUNTA(A:A) - 1

Count Cells with Errors

To count cells containing errors:

=SUMPRODUCT(ISERROR(A1:A100)*1)

Or count specific error types:

=SUMPRODUCT(ISNA(A1:A100)*1)  // Count #N/A errors

Common Mistakes to Avoid

  1. Using COUNT instead of COUNTA: COUNT only counts numbers, so text cells won't be counted. Use COUNTA to count all non-empty cells.

  2. Not understanding COUNTBLANK: COUNTBLANK counts cells that are truly empty, not cells with spaces or formulas returning "".

  3. Incorrect criteria syntax in COUNTIF: Use quotes for text criteria: "Complete", not Complete. Use quotes for operators: ">100", not >100.

  4. Counting entire column with header: When using COUNTA(A:A), remember it counts the header too. Use COUNTA(A2:A) or subtract 1.

  5. Mixing up COUNT and COUNTA: COUNT is for numbers only, COUNTA is for all non-empty cells.

Tips for Better Cell Counting

  • Use COUNTA for general counting: It's the most versatile function for counting cells with data.

  • Use COUNTIF/COUNTIFS for conditional counting: When you need to count cells based on criteria.

  • Combine functions for complex counts: You can nest functions or combine them for sophisticated counting.

  • Use SheetXAI for complex scenarios: When counting gets complicated, SheetXAI can generate the right formula automatically.

  • Test your formulas: Always verify your count formula works correctly with a small test range first.

  • Understand your data: Know whether you're dealing with numbers, text, or mixed data to choose the right function.

Counting Cells in Filtered Data

If you have filtered data and want to count only visible cells:

=SUBTOTAL(103, A1:A100)  // Counts visible non-empty cells
=SUBTOTAL(102, A1:A100)  // Counts visible numbers

The SUBTOTAL function with different function numbers counts only visible (non-filtered) cells.

Counting Cells Across Multiple Sheets

To count cells across multiple sheets:

=COUNTA(Sheet1!A:A) + COUNTA(Sheet2!A:A) + COUNTA(Sheet3!A:A)

Or use a more dynamic approach with INDIRECT if sheet names are in cells.

Conclusion

Now you know multiple ways to count cells in Google Sheets. The COUNTA function is usually the best choice for counting cells with data, while COUNTIF and COUNTIFS are perfect for conditional counting. COUNTBLANK helps you identify missing data.

But for those times when you just want the answer without thinking about which function to use, SheetXAI can count cells for you with a simple command. Just describe what you want to count, and SheetXAI will generate the right formula automatically.

Read Next

An illustration of a rectangular browser window displaying a spreadsheet-like grid with cells containing bright blue horizontal bars. A red callout box with black text that reads "Company.com" points to one of the cells. A large magnifying glass, with a grey lens and a dark gray handle with a red accent, is positioned over the right side of the grid, enlarging the cells beneath it. The browser window has three circular buttons (red, blue, white) on its top left. Minimalist abstract shapes, including lines and circles in dark gray, surround the main illustration on a white background. The overall color palette includes dark purple, red, light blue, and white.

How to Extract Domains from Emails in Excel (Without Formulas)

Extract domains from emails in Excel without complex formulas! Learn the easy, no-formula method and a powerful tool to clean your data fast. Get company.com from john@company.com effortlessly.

An illustration of a rectangular browser window displaying a spreadsheet-like grid with cells containing bright blue horizontal bars. A red callout box with black text that reads "Company.com" points to one of the cells. A large magnifying glass, with a grey lens and a dark gray handle with a red accent, is positioned over the right side of the grid, enlarging the cells beneath it. The browser window has three circular buttons (red, blue, white) on its top left. Minimalist abstract shapes, including lines and circles in dark gray, surround the main illustration on a white background. The overall color palette includes dark purple, red, light blue, and white.

How to Extract Domains from Emails in Google Sheets (Without Formulas)

Extract domains from emails in Google Sheets effortlessly! Discover how to do it without formulas using AI, or master the LEN, SEARCH, and RIGHT functions for precise extraction.

Illustration for How to Separate First and Last Names in Excel

How to Separate First and Last Names in Excel

Separate first & last names in Excel! Learn manual formulas (LEFT, RIGHT, SEARCH) or instantly split names with SheetXAI. Save time & get accurate results fast.

Boost your productivity today.
Start automating your spreadsheets.

Join thousands of professionals saving hours every week. No credit card required to start.

Learn more