COUNTIF In Google Sheets

COUNTIF is one of the most useful Google Sheets functions because counting is one of the most common tasks in spreadsheet work. Teams constantly need to know how many rows match a certain category, how many tasks are still open, how many invoices are unpaid, how many records belong to one region, or how many values meet a simple rule.

That is exactly what COUNTIF helps solve.

Instead of manually scanning rows or building long, repetitive formulas, COUNTIF gives you a simple way to count matching cells based on one condition. That makes it a very practical function for reporting, dashboards, trackers, analysis tabs, and day-to-day spreadsheet work.

This guide explains what COUNTIF is, how it works in Google Sheets, why it matters, how to use it with practical examples, and what common mistakes to avoid when counting values in real spreadsheet workflows.

Overview

COUNTIF counts how many cells in a range match one condition.

The basic syntax looks like this:

=COUNTIF(range, criterion)

This means:

• range is the set of cells you want to check
• criterion is the rule that decides what should be counted

A simple example looks like this:

=COUNTIF(A2:A20,"Open")

This means:

• look at cells A2 to A20
• count how many of them equal Open

That is the core idea of COUNTIF.

It lets Google Sheets count matching values quickly without manual review.

What COUNTIF actually does

COUNTIF checks each cell in a range against one rule and counts how many cells meet that rule.

That rule might be:

• a text value
• a number
• a comparison like greater than
• a date-style condition
• a wildcard pattern
• a duplicate check setup

This makes COUNTIF especially useful when you need a simple metric from a larger table.

For example:

• how many rows are Open
• how many values are above 1000
• how many customers are in the North region
• how many items contain the word Urgent
• how many blank cells exist in a required field

These are very common spreadsheet questions.

Why COUNTIF matters so much

A lot of reporting is not about adding numbers. It is about counting records.

For example, a manager may want to know:

• how many tickets are still unresolved
• how many new leads came in this week
• how many projects are overdue
• how many employees belong to one team
• how many invoices remain unpaid
• how many records are duplicates

Without COUNTIF, users often waste time:

• scanning the table manually
• filtering repeatedly
• counting by eye
• building more complicated logic than necessary

COUNTIF matters because it turns those counting questions into reusable spreadsheet formulas.

That is why it appears so often in reporting and dashboard logic.

The COUNTIF syntax explained

Here is the structure again:

=COUNTIF(range, criterion)

Range

This is the group of cells you want to test.

Examples:

• A2:A100
• B:B
• Sheet2!C2:C50

COUNTIF checks each cell in this range.

Criterion

This is the condition that decides what to count.

Examples:

• "Open"
• ">1000"
• "North"
• "*urgent*"
• "" for blanks in specific use cases

This is where most COUNTIF logic lives.

Understanding criteria is the main skill behind using COUNTIF well.

A simple COUNTIF example

Suppose column A contains task statuses:

Status
Open
Closed
Open
Open
Pending

To count how many tasks are Open:

=COUNTIF(A2:A6,"Open")

Result: 3

This is one of the most common COUNTIF use cases.

Counting text values

COUNTIF works very well with text categories.

Examples:

• Open
• Closed
• Finance
• North
• Urgent
• Approved

For example:

=COUNTIF(B2:B100,"Finance")

This counts how many cells in column B equal Finance.

This is useful in:

• department summaries
• region reports
• tracker status counts
• category-based dashboards

Counting numbers

COUNTIF can also count numeric matches.

For example, if column C contains scores and you want to count how many equal 100:

=COUNTIF(C2:C100,100)

This counts exact matches for the number 100.

Numeric COUNTIF patterns are useful in:

• performance tracking
• score distributions
• budget checks
• quantity summaries
• KPI thresholds

Counting with comparison operators

One of the most practical COUNTIF patterns is using conditions such as:

• greater than
• less than
• greater than or equal to
• less than or equal to

Examples:

=COUNTIF(C2:C100,">1000")

This counts how many values are greater than 1000.

=COUNTIF(C2:C100,"<=50")

This counts how many values are 50 or lower.

This is useful for:

• overdue counts
• high-value transactions
• low stock checks
• threshold reporting
• outlier counts

Counting blank cells

COUNTIF can also count blank cells.

Example:

=COUNTIF(A2:A100,"")

This counts how many cells in the range are blank.

This is useful for:

• incomplete records
• missing required fields
• quality-control checks
• dashboard completeness indicators

Blank counting is one of the most useful data-cleanup applications of COUNTIF.

Counting non-blank cells

To count cells that are not blank:

=COUNTIF(A2:A100,"<>")

This is useful when you want to know how many rows actually contain a value.

For example:

• number of completed entries
• number of filled owner fields
• number of rows with a region assigned

Using wildcards with COUNTIF

COUNTIF can use wildcards, which makes it very useful for text pattern matching.

The two most common wildcards are:

• * for any number of characters
• ? for a single character

Example:

=COUNTIF(A2:A100,"*urgent*")

This counts how many cells contain the word urgent anywhere in the text.

This is useful for:

• tag detection
• label scanning
• text-based issue classification
• partial-match reporting

Wildcards are one of the most practical COUNTIF features for messy real-world text.

Counting duplicates with COUNTIF

COUNTIF is also often used in duplicate logic.

For example, if column A contains invoice numbers or customer IDs, COUNTIF can help show whether a value appears more than once.

A common pattern in row logic is:

=COUNTIF(A:A,A2)

This counts how many times the value in A2 appears in column A.

That is useful for:

• duplicate checks
• identifying repeated IDs
• cleaning datasets
• supporting conditional formatting

This is one of the most common quality-control uses of COUNTIF.

COUNTIF in reporting workflows

COUNTIF is especially valuable in reporting because many business questions are count questions.

For example:

• count of open tickets
• count of overdue tasks
• count of unpaid invoices
• count of leads by source
• count of employees by department
• count of rows over threshold
• count of records with missing data

COUNTIF makes those metrics easy to build into:

• dashboards
• summary tabs
• trackers
• KPI sheets
• shared operational views

That is why it is one of the highest-value spreadsheet functions to learn early.

Common business use cases

Finance

Finance teams use COUNTIF for:

• unpaid invoice counts
• budget-line counts by status
• transaction counts by category
• threshold-based exception counts
• data-completeness checks

Operations

Operations teams use COUNTIF for:

• open ticket counts
• overdue task counts
• delayed shipment counts
• site issue counts
• queue volume counts

Analytics

Analysts use COUNTIF for:

• category distributions
• duplicate checks
• tag presence counts
• threshold-based record counts
• quick audit metrics
• dashboard summary figures

These are everyday spreadsheet tasks.

Common COUNTIF mistakes

The criterion does not match the real data

A formula may look correct, but the source values may not match exactly.

Examples:

• using Open when the source says Open Case
• using North when the source says North Region
• using one spelling while the source uses another

Always check the actual values in the sheet.

Hidden spaces

A value such as:

• Open
• and Open

may look identical but behave differently.

This is especially common in copied data and imported exports.

Text-number mismatches

A value may look like a number but be stored as text.

This can affect counting if the criterion and source do not align properly.

Using COUNTIF when multiple conditions are needed

COUNTIF handles one condition.

If the business question depends on two or more conditions, a different formula approach is usually better.

This is important because many users try to force multi-condition logic into COUNTIF when the reporting need is actually more complex.

Counting the wrong range

If the range includes headers, extra rows, or the wrong column, the result may be misleading even though the formula is technically valid.

Step-by-step workflow

If you want to use COUNTIF well, this is a strong process.

Step 1: Define the question clearly

Ask: What exactly am I trying to count?

Examples:

• Open tasks
• Finance rows
• values above 1000
• blank fields
• rows containing a keyword

Step 2: Identify the correct range

Which column actually contains the values that matter for the count?

Step 3: Write the criterion carefully

Make sure the criterion matches the real source values or rule logic.

Step 4: Test known examples

Pick a small visible set of rows and confirm the formula matches the manual count.

Step 5: Review whether one condition is enough

If the report needs more than one rule, COUNTIF alone may not be the right tool.

Practical formula examples

Count Open rows

=COUNTIF(A2:A100,"Open")

Count Finance rows

=COUNTIF(B2:B100,"Finance")

Count values above 1000

=COUNTIF(C2:C100,">1000")

Count blanks

=COUNTIF(D2:D100,"")

Count non-blanks

=COUNTIF(D2:D100,"<>")

Count text containing a word

=COUNTIF(E2:E100,"*urgent*")

These patterns cover many practical reporting needs.

When COUNTIF is the better choice

COUNTIF is usually the better choice when:

• one condition is enough
• you need a fast report metric
• the logic should stay simple
• the output is a count, not a row subset
• the formula will support dashboards or summary tables

It is one of the best first formulas for spreadsheet reporting.

When another approach may be better

COUNTIF is not always the right answer.

Sometimes another method is better, such as:

• FILTER for row subsets
• QUERY for report-style grouping and summaries
• a pivot table for drag-and-drop counts
• more advanced logic when the business question depends on several conditions

The best choice depends on whether the need is:

• one simple count
• a filtered view
• a grouped report
• or a larger workflow

FAQ

What does COUNTIF do in Google Sheets?

COUNTIF counts how many cells in a range match one condition, such as a specific word, number, date pattern, or rule.

Why is COUNTIF useful in Google Sheets?

COUNTIF is useful because it helps users count categories, statuses, duplicates, threshold-based values, and simple report metrics without needing long manual logic.

Can COUNTIF work with text and numbers?

Yes. COUNTIF can count both text and numeric matches, including criteria like exact words, numbers above a threshold, or values that meet simple comparison rules.

Why is my COUNTIF formula returning the wrong result?

COUNTIF usually returns the wrong result because of mismatched criteria, hidden spaces, text-number inconsistencies, unexpected source values, or criteria syntax that does not match the actual data.

Final thoughts

COUNTIF is one of the most practical Google Sheets functions because counting matching values is such a common part of reporting and spreadsheet analysis.

Whether you are counting open tasks, unpaid invoices, duplicate IDs, values above thresholds, or rows containing certain labels, COUNTIF turns those questions into quick reusable formulas. That makes dashboards easier to build, summary tabs easier to maintain, and spreadsheet analysis much faster.

The key is not just memorizing the syntax.

It is understanding the logic behind the range and the criterion, and making sure the condition actually matches the source data. Once that clicks, COUNTIF becomes one of the most reliable building blocks for everyday spreadsheet reporting in Google Sheets.