Count Rows That Meet Multiple Conditions

Excel & Google Sheets

How it works

COUNTIFS counts rows where every condition is true at the same time. Here it counts rows where column A says Sales and column C says Overdue. Conditions come in pairs — a range, then the value it must match — and you can add up to 127 pairs. Text matching ignores case, and you can use comparison operators in quotes, like ">100" or "<"&TODAY() for dates before today.

A2:A20,"Sales"

First condition: column A must equal Sales.

C2:C20,"Overdue"

Second condition: column C must equal Overdue. Both must be true.

When to use it

Use COUNTIFS whenever a count has an "and" in it: status and department, region and month, score above X and below Y.

Common mistakes

• ✕Ranges are different sizes.

  ✓A2:A20 with C2:C99 returns #VALUE!. Every condition range must cover the same rows.

• ✕Writing comparison operators without quotes.

  ✓Conditions like >100 must be quoted: ">100". To compare against a cell, concatenate: ">"&F1.

• ✕Needing OR logic instead of AND.

  ✓COUNTIFS is AND-only. For "Sales OR Marketing", add two COUNTIFS together.