Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
COUNTIF vs COUNTIFS: how they differ in Excel and Sheets
Topic:COUNTIF & COUNTIFSExcel & Google Sheets
=COUNTIF(A2:A4,"Active")✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Status | Region |
| Active | North |
| Inactive | South |
| Active | North |
=COUNTIF(A2:A4,"Active")→2
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Use COUNTIF for a single condition: =COUNTIF(range,criteria) — e.g., =COUNTIF(A2:A100,"Active") counts rows where A is 'Active'.
- 2Use COUNTIFS for multiple conditions: =COUNTIFS(range1,criteria1,range2,criteria2) — conditions are AND logic (all must be true).
- 3Example: =COUNTIFS(A2:A100,"Sales",B2:B100,">1000") counts rows where department is Sales AND amount is over 1000.
- 4For OR logic (either condition), add separate COUNTIF calls: =COUNTIF(A2:A100,"Sales")+COUNTIF(A2:A100,"HR") — or use COUNTIFS with SUMPRODUCT for complex OR.
Tips
- COUNTIFS conditions use AND logic — all conditions must match for a row to be counted. For OR logic, use COUNTIF+COUNTIF or SUMPRODUCT with multiple conditions.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Can COUNTIF count cells that are not equal to a value?
Yes — use the <> operator: =COUNTIF(A2:A100,"<>Active") counts all cells that are not 'Active'.
How do I count unique values with COUNTIF?
Use =SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100)) — for each value, COUNTIF returns how many times it appears, 1/n gives the fractional weight, and SUMPRODUCT sums to the unique count.
More on COUNTIF & COUNTIFS
See all →Reference
COUNTIF
Count the cells in a range that meet a condition.
How-toHow to count cells not equal to a value
Use COUNTIF with the <> operator to count all cells that do not match a specific value.
Error fixFix COUNTIF
COUNTIF not working in Excel and Google Sheets — fix criteria syntax, data-type mismatches, wildcard errors, and case-sensitivity misunderstandings.
ReferenceCOUNTIFS
Count cells that meet several conditions simultaneously.
How-toHow to use COUNTIF with a wildcard
Count cells that contain, start with, or end with specific text using COUNTIF with * and ? wildcard characters.
Error fixFix Wrong count
COUNTIF returns the wrong count in Excel and Google Sheets — fix wildcard over-matching, duplicate rows, hidden rows, blank cell handling, and date format issues.