Google Sheets Countifs: Mastering Multiple Criteria

Understanding COUNTIF vs COUNTIFS

COUNTIF counts a single criterion; COUNTIFS counts multiple. In Google Sheets, consider when to use one vs the other. When you have more than one condition, COUNTIFS is the natural choice. If you only need a single condition, COUNTIF keeps things simple and readable. Below are simple illustrations to illustrate the difference, followed by practical examples you can adapt to your dataset.

The first formula counts rows where column A equals North. The second counts rows where A equals North and the date in column B is on or after Jan 1, 2026. In real sheets, you’ll mix text, numbers, and dates, and COUNTIFS handles them in a single call.

COUNTIFS Syntax Patterns

The COUNTIFS function uses pairs of range and criterion to build multi-criteria counts. The syntax is straightforward and scalable: you can add as many criterion pairs as your data requires. This makes COUNTIFS ideal for dashboards where drill-down filters are common.

This structure lets you add as many criteria as you need. Examples:

Both examples show how to combine conditions across columns. The first filters by region and a date threshold; the second uses a wildcard on text and ignores empty/non-matching statuses.

OR logic with COUNTIFS and sum

Sometimes you need A OR B conditions. COUNTIFS doesn't do OR by itself, but you can emulate it with sum of multiple COUNTIFS (or with array constants). This approach is common in lightweight dashboards where you want to report on multiple regions or categories without writing a full query.

Note that the first example leverages an array constant to count multiple regional conditions in a single call. The second is equivalent but explicit, which some users find easier to audit.

Complex criteria with SUMPRODUCT

When you need more than just equality or simple ranges, SUMPRODUCT shines by allowing arithmetic across multiple conditions, including numeric comparisons and non-contiguous criteria. This approach is powerful but can be harder to read at a glance, so document formulas clearly in dashboards.

This formula counts rows where A equals North, B is greater than 50, and C equals Open. SUMPRODUCT evaluates each condition across the same row indices and returns the total count.

Using FILTER and COUNTA as a count alternative

FILTER returns the subset of rows that satisfy all conditions, and COUNTA (or ROWS) counts the resulting rows. This can be intuitive when building interactive dashboards that show live counts based on user-selected criteria.

Both lines produce the same count in most data sets, with COUNTA counting non-empty values and ROWS counting the number of filtered rows.

Case sensitivity and wildcards

COUNTIFS is not case-sensitive by default, which is convenient for typical business data. If case sensitivity is required, you’ll need more advanced logic, such as using EXACT within an array context, or combining LOWER/UPPER to normalize text before comparison.

Wildcards can also extend matching, e.g., using * or ?. Note that wildcards apply within the criterion string for text fields.

Performance and best practices

As datasets grow, performance becomes a consideration. Prefer explicit ranges (A2:A100) over entire columns (A:A) in COUNTIFS and SUMPRODUCT. If you must use full columns, consider limiting the number of rows or using named ranges to bound calculations. This keeps sheets responsive in dashboards and reports.

Practical examples to copy into your dashboard

These templates demonstrate common multi-criteria patterns you’ll reuse:

Adapt ranges to your data model and replace constants with cell references for dynamic dashboards.

Debugging and common mistakes

Common issues include mismatched range sizes (criteria_range and range must have the same size) and using incompatible operators or text without quotes. Always ensure ranges align and criteria are properly quoted. If a count is unexpectedly zero, test each criterion separately to confirm which condition filters out all data.