Excel COUNTIFS: Count Rows With Several Rules
By Szabó Gergő · Updated
COUNTIFS counts rows where every criterion is true. Each criteria range must be the same size. It is the counting counterpart to SUMIFS.
Syntax and arguments
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)- criteria_range1
- The first range to test.
- criteria1
- The condition, such as "Paid", G2, or ">0".
- additional pairs
- Optional extra ranges and conditions, all applied together.
COUNTIFS examples
Status in C, dates in B, month start in G1.
=COUNTIFS(C2:C200,"Paid",B2:B200,">="&G1,B2:B200,"<"&EDATE(G1,1))All three tests must pass for a row to count.
Region in A, email in E, selected region in G2.
=COUNTIFS(A2:A200,G2,E2:E200,"<>")"<>" counts cells that are not empty.
Codes in A2:A500.
=COUNTIFS(A2:A500,"NY*")NY* matches any code that starts with NY.
Common mistakes
Ranges of different sizes
Every criteria_range must have the same number of rows and columns.
Forgetting to concatenate operators
Use ">="&G1 rather than ">=G1" when the threshold lives in a cell.
Counting the header
Start ranges at row 2.
COUNTIFS FAQ
How is COUNTIFS different from COUNTIF?
COUNTIF takes one range and one condition. COUNTIFS takes many pairs.
Can I use OR logic?
Add two COUNTIFS results, or use a FILTER/COUNTA approach for complex OR sets.
Does COUNTIFS count zeros?
Yes, a numeric zero is a value. Use "" or "<>" depending on whether blanks should count.