How to Use the COUNTIF Function in Google Sheets
A Google Sheet function we use often is COUNTIF, especially if we are working on a fairly simple dataset. For example, we use Google Sheets to keep track of the number of articles published on our site. At the bottom of our Sheet, we have a running tally of how many articles we have published to date, the number of articles per topic, and how many articles each team member has written. We use COUNTIF to help keep track of the last two. You can use COUNTIF for various purposes. Whether you're managing a classroom roster, tracking inventory, or analyzing survey responses, COUNTIF can help you quickly make sense of your data.
What Is the COUNTIF Function?
The COUNTIF function counts the number of cells in a range that meet a single condition (or “criteria”).
Syntax:
=COUNTIF(range, criterion)
range: The group of cells you want to evaluate.
Criterion: The condition by which you want to count cells. This can be a number, text, expression, or even a cell reference.
Why Use COUNTIF?
Use COUNTIF when you need to:
Count how many times a specific value appears.
Identify trends or outliers in a data set.
Summarize information quickly without filtering manually.
Track progress based on predefined benchmarks (like scores, attendance, or dates).
Real-World Examples of COUNTIF
📚 Example 1: Student Attendance Tracker
Imagine you're a teacher with a spreadsheet where column A contains student names and column B shows their attendance ("Present" or "Absent").
=COUNTIF(B2:B30, "Absent")
This formula will count how many students were marked "Absent" in your list.
🛒 Example 2: Inventory Status
You’re managing an inventory list where column A contains product names and column B indicates stock status ("In Stock", "Out of Stock").
=COUNTIF(B2:B100, "Out of Stock")
This counts all products that are currently out of stock—great for restocking alerts.
📈 Example 3: Customer Survey Results
You conducted a survey and collected feedback ratings from 1 to 5 in column C.
=COUNTIF(C2:C100, ">4")
This counts how many people rated your service as 5 (or greater than 4), helping you gauge high satisfaction levels.
🏡 Example 4: Real Estate Listings
In a sheet listing property prices in column D, you want to count how many are under $500,000:
=COUNTIF(D2:D100, "<500000")
This helps you quickly see how many listings fall within a budget.
Tips and Tricks
You can use wildcards for partial text matches:
=COUNTIF(A2:A50, "*Smith*")
This counts names containing the word "Smith".
Reference criteria from another cell:
=COUNTIF(B2:B100, E1)
This counts how many values in column B match what's in cell E1.
Common Errors to Watch Out For
Incorrect range or mismatched types: Make sure the range matches the data type you're evaluating.
Misplaced quotation marks: For text and logical operators (>, <, =, etc.), the entire condition must be inside quotes.
Wrap-Up
The COUNTIF function is a simple yet powerful way to extract meaningful insights from your Google Sheets data. From counting survey responses to tracking attendance, it saves time and makes your spreadsheets far more dynamic. Consider a pivot table if you are working with a complex dataset and need to summarize data by multiple dimensions.
