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.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
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.