Google Sheets COUNTIF or COUNTIFS: Which One to Use

In Google Sheets, use COUNTIF to count cells that meet one condition, and use COUNTIFS when a row must meet two or more conditions at the same time. For an OR condition—such as counting rows marked either Open or Pending—you can usually add two COUNTIF results.

Choosing between COUNTIF and COUNTIFS

The practical choice comes down to how many conditions must be satisfied. A single test, such as “How many cells say Open?”, calls for COUNTIF. A combined test, such as “How many rows say Open and are in the East region?”, calls for COUNTIFS.

It helps to distinguish AND from OR. With AND logic, every condition must be true for a row to count. With OR logic, any one of the listed conditions is enough. COUNTIFS applies AND logic across its condition pairs; it does not mean “count if this condition or that condition is true.” For simple OR tests, add separate counts instead.

One condition: use COUNTIF.
Several conditions that must all be true: use COUNTIFS.
Several alternative values in one column: add separate COUNTIF results, taking care not to count a record twice if the tests can overlap.

How COUNTIF works

COUNTIF takes a range to inspect and one criterion to apply. The criterion can be a text value, a number, a comparison such as “greater than 10,” or a reference to a cell containing the criterion. It counts matching cells; it does not add their values.

For example, suppose A2:A8 contains these statuses, in order: Open, Closed, Open, Pending, Closed, Open, Pending. With those statuses in A2:A8, the following formula counts the Open entries and returns 3:

=COUNTIF(A2:A8,"Open")

The range and criterion are separate arguments. Text criteria go inside quotation marks, while a cell reference used as a criterion is generally written without quotation marks. COUNTIF is not case-sensitive, so Open and OPEN are treated as the same text for counting purposes.

Using COUNTIFS for AND conditions

COUNTIFS lets you pair each range with a criterion. A row is counted only when it meets every listed test. Each condition range must have the same dimensions as the others, so use corresponding ranges that cover the same rows.

Imagine a small table with statuses in A2:A8 and regions in B2:B8. The rows are Open/East, Closed/West, Open/West, Pending/East, Open/East, Closed/East, and Open/West. With those columns and rows, this formula counts records that are both Open and in the East region; it returns 2:

=COUNTIFS(A2:A8,"Open",B2:B8,"East")

You can add more range-and-criterion pairs when you need another AND test. For example, suppose the same table has quantities in C2:C8, with values 5, 2, 7, 4, 3, 6, and 1. With status in A2:A8 and quantity in C2:C8, this formula counts Open rows with a quantity of at least 4; it returns 2:

=COUNTIFS(A2:A8,"Open",C2:C8,">=4")

That result comes from the rows with quantities 5 and 7. The comparison operator belongs inside the criterion text. This is useful for combining categories with numeric thresholds, dates, or other tests.

Counting OR conditions

For alternatives in one column, add individual counts. Using the status list in A2:A8 above, the following formula counts rows that are either Open or Pending. There are three Open entries and two Pending entries, so it returns 5:

=COUNTIF(A2:A8,"Open")+COUNTIF(A2:A8,"Pending")

This works cleanly because a single cell cannot equal both Open and Pending. But addition can overcount if the conditions overlap. For example, two text tests using wildcards might both match the same cell. In that case, adding the counts counts the shared match twice. Consider whether the conditions are mutually exclusive before using this pattern.

For OR conditions across different columns, write down the intended row-level rule first. “East region or quantity over 5” means count a row once if either test is true, including a row that meets both. A simple sum of separate counts would count rows meeting both tests twice. That case needs a method that handles overlap, so do not assume every OR rule can be solved by adding two counts without adjustment.

Text, numbers, comparisons, and cell references

Criteria can check exact text or values, and comparison operators can express ranges. For numeric tests, operators such as >, >=, <, and <= are placed in quotation marks with the comparison value. If the value is in a cell, join the operator to the reference with &.

Suppose C2:C8 contains the quantities listed earlier, and E1 contains the number 4. With quantities in C2:C8 and the threshold in E1, this formula counts values greater than or equal to the threshold and returns 4:

=COUNTIF(C2:C8,">="&E1)

The four qualifying values are 5, 7, 4, and 6. This approach is convenient when someone needs to change the threshold without editing the formula. For dates, use a real date value or a cell containing a date, rather than relying on text that only looks like a date. Date interpretation can depend on the spreadsheet’s locale and formatting.

Wildcards and partial text matches

In text criteria, an asterisk (*) stands for any number of characters, including none, and a question mark (?) stands for one character. These are useful for counting entries that share a word or pattern rather than matching an entire cell exactly.

Suppose D2:D6 contains North, Northeast, South, Northwest, and East. With those values in D2:D6, this formula counts entries that begin with North; it returns 3:

=COUNTIF(D2:D6,"North*")

To match a literal asterisk or question mark in cell text, put a tilde (~) before the wildcard character in the criterion. For example, a criterion intended to find a literal question mark uses ~?. This distinction matters when spreadsheet values contain symbols rather than text patterns.

Common COUNTIF and COUNTIFS problems

A formula can return zero even when the sheet appears to contain a match. Check for extra spaces, inconsistent spelling, or a criterion that does not match the underlying value. A cell may also contain a number stored as text, or a date stored as text, which can behave differently from a true numeric value or date.

Wrong logic: COUNTIFS requires all of its criteria to match. Use a different approach for OR logic.
Range sizes differ: In COUNTIFS, keep each criteria range aligned and the same size.
Comparison typed as plain text: Put the operator and value together in the criterion, such as ">=4".
Unintended wildcard: A star or question mark in a text criterion can match more than a literal symbol.
Ranges include headers: Start at the first data row if the header should not be counted.

When a result looks wrong, test the simplest condition first, then add criteria one at a time. Also check whether a filter is hiding rows: COUNTIF and COUNTIFS count matching cells in the specified range even if some rows are hidden by a filter.

How Google Sheets compares with Excel

Google Sheets and Microsoft Excel both provide COUNTIF and COUNTIFS, and the basic syntax and AND-versus-OR distinction are very similar. A formula such as the examples above will generally be familiar to Excel users as well. Differences can arise from spreadsheet locale settings, which may change the argument separator from a comma to a semicolon, and from features or interface details that vary between applications.

In Google Sheets in a web browser, enter a formula in a cell or in the formula bar and press Enter. If a formula with commas is rejected, check the file’s locale settings before changing the function; separators can depend on locale. Excel has its own menus and settings, but the underlying choice between one criterion, multiple AND criteria, and OR alternatives remains the same.

Frequently Asked Questions

Is COUNTIF or COUNTIFS better in Google Sheets?
Neither is universally better. Use COUNTIF for one condition and COUNTIFS when all of multiple conditions must be met. Choosing the simpler function makes the formula’s logic easier to understand.

How do I count cells that match either of two words?
For two distinct, mutually exclusive values in one column, add two COUNTIF results. If the criteria can both match the same cell, account for that overlap so the same entry is not counted twice.

Does COUNTIFS mean OR?
No. COUNTIFS applies AND logic: each range-and-criterion pair must match for a row to count. It does not count a row when just one of the criteria is true.

Can COUNTIF count partial text?
Yes. Use an asterisk for any sequence of characters or a question mark for one character. Prefix a wildcard with a tilde when you need to search for that symbol literally.

Why does COUNTIF return zero when the text looks right?
Check for extra spaces, misspellings, mismatched data types, and criteria that accidentally use wildcard characters. Also confirm that the formula refers to the intended range and that the cells contain actual values rather than different underlying data.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.