How to Count Cells with Two Conditions in Google Sheets

Q: What is the difference between COUNTIF and COUNTIFS?

COUNTIF tests one condition; COUNTIFS tests two or more that must all be true.

Q: How do I count with an OR instead of AND?

Add two COUNTIFS: =COUNTIFS(...) + COUNTIFS(...). COUNTIFS itself only does AND.

Q: Can I use a cell for the number test?

Yes — join the operator to it: ">"&G1 compares to whatever G1 holds.

Use COUNTIFS: =COUNTIFS(A:A,"Open",B:B,">100") counts rows that meet both tests at once.

=COUNTIFS(A:A,"Open",B:B,">100")

Each pair is a range and its criterion; a row is counted only if every pair is true.

How it works

COUNTIFS extends COUNTIF to several conditions joined by AND. You pass range/criterion pairs, and only rows satisfying all of them are counted. Criteria can be text, numbers, comparisons like ">100", or cell references. For an OR of two conditions, add two COUNTIFS together instead.

Variations

Count within a date range

=COUNTIFS(C:C,">="&D1,C:C,"<="&D2)

D1 and D2 hold the start and end dates; & joins the operator to the cell reference.

Match text containing a word

=COUNTIFS(A:A,"*north*",B:B,"Paid")

Asterisks are wildcards, so any value containing north counts.

Reference a criterion cell

=COUNTIFS(A:A,F1,B:B,">"&G1)

Keeps the formula tidy and lets you change criteria without editing it.

Examples

Scenario	Formula
Open tickets above 100 in value	`=COUNTIFS(A:A,"Open",B:B,">100")`
Sales in a chosen month	`=COUNTIFS(C:C,">="&D1,C:C,"<="&D2)`

FAQ

What is the difference between COUNTIF and COUNTIFS?

COUNTIF tests one condition; COUNTIFS tests two or more that must all be true.

How do I count with an OR instead of AND?

Add two COUNTIFS: =COUNTIFS(...) + COUNTIFS(...). COUNTIFS itself only does AND.

Can I use a cell for the number test?

Yes — join the operator to it: ">"&G1 compares to whatever G1 holds.

Related formulas

→ See all 34 Google Sheets formulas in one cheat sheet