COUNTIF vs COUNTIFS
COUNTIF counts cells that meet one condition. COUNTIFS counts rows that meet several conditions at once. COUNTIFS with a single pair does exactly what COUNTIF does, so many people just use COUNTIFS everywhere.
The differences
| Feature | COUNTIF | COUNTIFS |
|---|---|---|
| Conditions | One | One or more, all of which must be true |
| Arguments | range, criteria | criteria_range1, criteria1, criteria_range2, criteria2, … |
| OR logic (East or West) | Add two COUNTIFs | Add two COUNTIFS |
| Ranges | One range | Every criteria range must be the same size |
| Available in | Every version | Every version |
Same data, side by side
One sample sheet, each approach in its own cell. Results are calculated by our formula engine.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| F2 | =COUNTIF(A2:A8,"East") | 4 | One condition: orders from East |
| F3 | =COUNTIFS(A2:A8,"East",B2:B8,"Shipped") | 3 | Two conditions: East and shipped |
| F4 | =COUNTIFS(A2:A8,"East",C2:C8,">500") | 3 | A text condition and a number condition together |
Try it live
Change the inputs or rewrite a formula and compare the results.
When to use each
Choose COUNTIF when
There is one condition and you want the shortest formula.
Choose COUNTIFS when
There are two or more conditions, or you expect to add one later.
Practice COUNTIFS
From lesson 8, COUNTIFS, SUMIFS & AVERAGEIFS. Graded instantly, then re-checked on changed data.
Count with a threshold
G6, count orders in the region from G2 with 50 or more units.The functions
COUNTIFCOUNTIF counts the cells in a range that meet one condition: tickets marked "Open", scores of 70 or more, product names containing "lamp". Every current version.COUNTIFSCOUNTIFS counts the rows where every condition is true: orders that are from the East region and shipped, deals between $500 and $1,000. Every current version.
Questions
How do I count with OR in COUNTIFS?
Add two counts: =COUNTIF(A2:A8,"East")+COUNTIF(A2:A8,"West"). One COUNTIFS always means AND.
Why does COUNTIFS return #VALUE!?
The criteria ranges are different sizes. Make every range cover the same rows, such as A2:A8 and B2:B8.
Last reviewed October 2026.