How do I count rows that match a condition?

COUNTIF counts how many cells in a range meet one condition: =COUNTIF(range, criteria). COUNTIFS counts how many rows meet several conditions across different columns at once: =COUNTIFS(range1, criteria1, range2, criteria2, ...).

Formula (Excel and Google Sheets)

=COUNTIFS(A:A, "Open", B:B, ">"&TODAY()-30)

COUNTIF answers "how many cells in this range match this one condition." The example above counts how many rows have "Open" in column A AND a date in column B more recent than 30 days ago, using COUNTIFS for the two conditions together.

Unlike SUMIF, both COUNTIF and COUNTIFS keep the same argument order (range, criteria, repeated), which makes COUNTIFS a little easier to remember than SUMIFS.

Step by step

1
For one condition, use COUNTIF
=COUNTIF(range, criteria).
2
For more than one condition, use COUNTIFS
=COUNTIFS(range1, criteria1, range2, criteria2, ...). Every range must be the same size.
3
Use comparison operators in quotes for numbers and dates
e.g. ">100", "<="&TODAY(), or "<>"&"" to count non-blank cells.

Common questions

How do I count blank or non-blank cells?

Use COUNTBLANK(range) for blanks, or COUNTIF(range, "<>") for non-blank cells.

Can COUNTIF match partial text?

Yes, with wildcards: COUNTIF(range, "*keyword*") counts cells containing that keyword anywhere in the text.

Related formulas

How to Use SUMIF and SUMIFS in Excel and Google Sheets

How to Remove Duplicate Rows in Excel and Google Sheets

How to Write a Nested IF Formula (and When to Avoid It)

How to Count Rows That Match Multiple Criteria with COUNTIFS

From the blog

How to Make Sense of Your Spreadsheet Data Without Being an Analyst

Want a formula written for your exact spreadsheet, not a generic example?

Try the free formula generator