Open tasks per owner
=COUNTIFS(Status2:Status, "Open", Owner2:Owner, "Sam") counts rows where both columns match.
What is COUNTIFS in Google Sheets?
COUNTIFS counts how many rows satisfy every criterion at once. You pass criteria_range1, criterion1, criteria_range2, criterion2, and so on. Unlike COUNTIF, there is no separate count range: each pair tests the same row index across the sheet.
When to use it
Use COUNTIFS for multi-filter counts: how many tickets are Open and assigned to Jordan, or how many rows are both Active and in the West region.
When to skip it
Skip COUNTIFS for one simple test. COUNTIF is enough. When you need the sum of values that match, use SUMIFS instead.
How it works
-
1
List criteria_range and criterion pairs with no leading sum column.
-
2
All criteria_ranges must share the same height and row alignment.
-
3
A row increments the count only when every test on that row is TRUE.
-
4
Wildcards and comparisons work the same as COUNTIF inside each criterion.
-
5
Reference settings cells for dashboard-driven counts without editing formulas.
-
6
Add pairs for each extra AND condition on another column.
Examples in Google Sheets
Scores in a band
=COUNTIFS(Score2:Score, ">=70", Score2:Score, "<80") counts C grades on one column twice.
Region and checkbox
=COUNTIFS(Region2:Region, G1, Include2:Include, TRUE) counts checked rows in the selected region.
Build this without starting from a blank cell
Use a Better Sheets tool for COUNTIFS, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Using COUNTIFS when ranges are different lengths, which breaks row pairing.
- Expecting OR across columns from one formula. Split into multiple COUNTIFS added together.
- Testing the wrong column twice when the second rule should reference another field.
- Counting header rows because the range starts on row 1 with a label cell.
- Replacing COUNTIFS with long helper columns when a single formula would stay clearer.
Frequently asked questions
- COUNTIFS vs COUNTIF?
- COUNTIF tests one range. COUNTIFS requires every paired range and criterion to pass on the same row.
- COUNTIFS vs SUMIFS?
- COUNTIFS counts matching rows. SUMIFS adds numeric values from sum_range for matching rows.
- COUNTIFS with OR?
- Use multiple COUNTIFS added together or QUERY. One COUNTIFS call is AND only.
- COUNTIFS same range twice?
- Yes for between tests, such as score >= 70 and score < 80 on one column.
- COUNTIFS with wildcards?
- Each criterion string can include * and ? like COUNTIF.
- COUNTIFS blank cells?
- Use "" as criterion on the range where blank should match.
- COUNTIFS vs FILTER?
- COUNTIFS returns one number. FILTER returns the matching rows as a spill range.
- Why COUNTIFS too low?
- One strict criterion filters out rows you expected, often a date or status typo.
Related Tutorials
Watch how COUNTIFS works
3 More Ways to Count in Google Sheets
Related blog posts
Guides that explain COUNTIFS in more depth.
The Most Popular Google Sheet Tutorial on the IF() Formula
The IF() formula is a logical function that makes it easy for users to compare a certain value and an expected value. It takes a look at a value to see if basic conditions are met.
Read post →5 Magical Ways To Use the IF() Formula in Google Sheets
5 ways to use the IF function in Google Sheets ISBLANK() Checkbox AND() OR() IF(IF())
Read post →9 Tutorials to Help You Use the IF() Formula Better
Build tools like timers and savings thermometers. Unlock the power of the =IF() formula with the comprehensive tutorials on Better Sheets!
Read post →Related glossary terms
COUNTIF
COUNTIF returns how many cells in a range meet a single criterion. It powers KPI tiles like open ticket count, rows flagged Yes, or cells above a quota. The criterion follows the same string, comparison, and wildcard rules as SUMIF.
Read guide →SUMIFS
SUMIFS adds values in sum_range only when every criteria_range passes its paired test. Argument order is sum_range first, then alternating criteria_range and criterion pairs. All conditions use AND logic: every test must be TRUE on the same row.
Read guide →FILTER
FILTER returns only the rows from a range that meet one or more conditions. Conditions are boolean arrays the same height as the data. FILTER spills results automatically, so it replaced many INDEX/SMALL helper-column patterns for live subsets.
Read guide →Google Sheets formula syntax
Every formula starts with = followed by a function name and arguments in parentheses. Ranges use A1 notation, text sits in quotes, and operators like + and & combine values. Sheets recalculates when inputs change, so syntax errors show as #NAME? or #ERROR! in the cell.
Read guide →Done reading about COUNTIFS?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.