If you’ve ever looked at a list in Google Sheets and thought, “How many times does this value appear?” or “How many rows meet specific conditions?”—you’re in the right place. The COUNTIF and COUNTIFS functions are powerful tools that help you quickly count cells based on one or more criteria. Whether you’re a beginner managing survey responses, attendance, or sales data, learning these functions can save time and boost your data analysis skills.
In this guide, you’ll learn how COUNTIF and COUNTIFS work, how they differ, and how to use them with real-life examples and formulas that are beginner-friendly and easy to apply.
What is COUNTIF?
COUNTIF is used when you want to count the number of cells in a range that meet a single condition.
=COUNTIF(range, criterion)- range: The group of cells you want to evaluate.
- criterion: The condition you’re checking against (e.g., “Apples”, “>=50”).
What is COUNTIFS?
COUNTIFS lets you apply multiple conditions. It counts the number of cells that meet all the specified criteria across multiple ranges.
=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2], ...)- criteria_range: The ranges to evaluate.
- criterion: The condition to apply to each corresponding range.
Real-Life Example: Track Student Attendance
Let’s say you’re a teacher and want to track attendance data. Here’s a sample sheet of attendance records:
| Name | Date | Status |
|---|---|---|
| Alice | 2024-04-01 | Present |
| Bob | 2024-04-01 | Absent |
| Alice | 2024-04-02 | Absent |
| Bob | 2024-04-02 | Present |
Use COUNTIF: Count How Many Times “Alice” Was Present
=COUNTIFS(A2:A5, "Alice", C2:C5, "Present")Result: 1
Use COUNTIF: Count All “Absent” Entries
=COUNTIF(C2:C5, "Absent")Result: 2
Popular Use Cases for COUNTIF and COUNTIFS
- Track how many orders are above a certain value.
- Count survey responses that match specific answers.
- Monitor inventory items that are out of stock.
- Identify how many employees submitted timesheets late.
Tips for Using COUNTIF and COUNTIFS
- Use wildcards like
*(any characters) or?(one character) for partial matches. - Criteria like
">100"or"<=75"must be in quotes. - COUNTIFS requires all criteria to be TRUE for a row to be counted.
- Use cell references instead of hardcoded values for dynamic formulas (e.g.,
=COUNTIF(B2:B100, E1)).
Quick-Reference Cheat Sheet
| Task | Formula |
|---|---|
| Count all cells with “Apples” | =COUNTIF(A2:A100, “Apples”) |
| Count cells with values over 50 | =COUNTIF(B2:B100, “>50”) |
| Count entries with “Present” status | =COUNTIF(C2:C, “Present”) |
| Count “Apples” sold in “East” region | =COUNTIFS(A2:A100, “Apples”, B2:B100, “East”) |
| Count items under $100 and in stock | =COUNTIFS(C2:C100, “<100”, D2:D100, “In Stock”) |
With COUNTIF and COUNTIFS, you can go beyond basic counts and start drawing real insights from your data in Google Sheets. Whether you’re sorting feedback, tracking performance, or managing lists, these functions are easy to learn and endlessly useful.
Try them out on your own spreadsheets—your data will start working smarter for you!