COUNTIFS in Google Sheets: A Practical Guide
Master multi-criteria counting in Google Sheets with COUNTIFS. Learn syntax, practical examples, tips, and best practices for reliable data analysis.
COUNTIFS in Google Sheets counts cells across multiple ranges that meet all given criteria. It extends COUNTIF by allowing more than one condition, enabling powerful data filtering without helper columns. This guide shows syntax, examples, and common pitfalls to master multi-criteria counting quickly. Countifs google sheets combines multiple criteria across ranges, making it ideal for complex data analyses.
What COUNTIFS does in Google Sheets
COUNTIFS is a foundational tool for power users who need to count rows that satisfy more than one condition. According to How To Sheets, COUNTIFS is a go-to function for multi-criteria filtering in Google Sheets. It lets you intersect multiple criteria across different columns without creating auxiliary columns or complex filters. This is especially useful for QA checks, sales analyses, or inventory audits where you must verify several conditions at once. Below are practical examples that showcase common patterns and how to interpret results.
=COUNTIFS(B2:B100, "Complete", C2:C100, "High")This counts all rows where the Status column equals "Complete" and the Priority column equals "High".
=COUNTIFS(A2:A100, ">="&DATE(2024,1,1), A2:A100, "<="&DATE(2024,12,31), B2:B100, "Delivered")This counts rows in 2024 where the Status is Delivered. Note: All ranges must share the same size for COUNTIFS to work correctly.
Line-by-line commentary:
- The first criterion pairs a range with a value, filtering by status.
- The second criterion uses a date range to constrain the time period.
- The final criterion adds a delivery status filter. We rely on the same row structure across ranges.
Steps
Estimated time: 15-25 minutes
- 1
Identify data ranges
Identify which columns will serve as criterion ranges (e.g., Status, Priority) and which will hold the data you want to count. Define clear headers and ensure consistent data types across columns.
Tip: Use named ranges to simplify formulas and reduce errors. - 2
Write a base COUNTIFS
Start with a simple two-criterion COUNTIFS to validate your ranges and criteria. Verify the result against a small, known sample.
Tip: Double-check that all ranges are the same size. - 3
Test date and numeric criteria
Add date or numeric criteria, using operators like >=, <=, >, <. Always concatenate dates with DATE() or TODAY() as needed.
Tip: Be mindful of date serial numbers and time components. - 4
Combine dynamic criteria
Reference cells for criteria so your formula adapts as data changes. For example, refer to a cell that stores the threshold.
Tip: Avoid hard-coding values when possible. - 5
Validate with edge cases
Check how the formula behaves with blank cells, unexpected data types, or trailing spaces.
Tip: Trim leading/trailing spaces if you encounter mismatches.
Prerequisites
Required
- Required
- Internet connectionRequired
- Required
Optional
- Optional: familiarity with named rangesOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy selected cells or formula output | Ctrl+C |
| PastePaste cells or results into a range | Ctrl+V |
| FindFind text within the sheet | Ctrl+F |
FAQ
What is COUNTIFS in Google Sheets?
COUNTIFS counts rows that meet multiple criteria across specified ranges. It’s the multi-criteria counterpart to COUNTIF and is ideal for filtering datasets by several conditions.
COUNTIFS counts rows that satisfy several conditions at once, just like multi-criteria filtering in a spreadsheet.
Can COUNTIFS handle dates?
Yes. Use comparison operators with date values. For example, criteria like ">=DATE(2024,1,1)" and "<=DATE(2024,12,31)" work with a date column.
Absolutely—you can count dates within a range by using date comparisons in COUNTIFS.
Do the ranges have to be the same size?
Yes. All criteria ranges must be the same size and shape. If they differ, Google Sheets will return an error.
All ranges used in COUNTIFS must have identical dimensions.
Can I use wildcards with COUNTIFS?
Yes. You can use wildcards like * and ? in text criteria to match patterns within cells.
Wildcards help you match text patterns when counting with COUNTIFS.
How does COUNTIFS differ from COUNTIF?
COUNTIF handles a single criterion, while COUNTIFS supports multiple criteria across several ranges. COUNTIFS is more powerful for complex filtering.
COUNTIF is for one condition; COUNTIFS lets you count with many conditions at once.
The Essentials
- Master multi-criteria counting with COUNTIFS
- Keep all ranges the same size to avoid errors
- Leverage named ranges for readability
- Use wildcards for flexible text matching