Use COUNTIF in Excel to count cells that meet one condition, such as tasks marked Done. Use COUNTIFS when a row must meet several conditions, such as tasks owned by Maya that are also Done. This guide gives copy-ready formulas, a small office tracker, expected counts, and checks for misleading results.
What is the difference between COUNTIF and COUNTIFS?
COUNTIF has two arguments: =COUNTIF(range,criteria). The range is the set of cells to examine; the criterion is the value or rule to match. For example, =COUNTIF(C2:C9,"Done") counts cells containing the status Done. The result is a count of matching cells, not the sum of any values in those rows.
COUNTIFS accepts pairs of criteria ranges and criteria: =COUNTIFS(range1,criterion1,range2,criterion2). It counts a row only when every supplied condition matches. Thus, =COUNTIFS(B2:B9,"Maya",C2:C9,"Done") counts Maya’s completed tasks, rather than all tasks belonging to Maya or all completed tasks.
Microsoft documents the COUNTIFS syntax and equal-sized range requirement and explains counting numbers or dates by criteria. The examples below use comma separators; some Excel regional settings use semicolons.
Set up a sample office task tracker
Create a worksheet called Tracker. Put Request ID, Owner, Status, Due Date, and Priority in cells A1:E1. Enter these eight illustrative requests in rows 2 through 9. Format column D as a real Excel date, not a text label that only resembles a date. The example uses fictional people and deadlines.
| Row | Request ID | Owner | Status | Due Date | Priority |
|---|---|---|---|---|---|
| 2 | T-101 | Maya | Open | 2026-10-20 | High |
| 3 | T-102 | Leo | Done | 2026-10-20 | Normal |
| 4 | T-103 | Maya | Blocked | 2026-10-21 | High |
| 5 | T-104 | Priya | Done | 2026-10-22 | Normal |
| 6 | T-105 | Leo | Open | 2026-10-23 | High |
| 7 | T-106 | Maya | Done | 2026-10-24 | Normal |
| 8 | T-107 | Priya | Open | 2026-10-24 | Normal |
| 9 | T-108 | Leo | Blocked | 2026-10-25 | High |
There are three Open, three Done, and two Blocked requests. Maya owns three requests, Leo owns three, and Priya owns two. Keep this manual tally beside the formulas while learning: a small, known dataset makes it easier to spot a wrong range or a misspelled criterion.
Example 1: Count tasks with one status using COUNTIF
In G1 type Status and in H1 type Count. Enter Open, Done, and Blocked in G2:G4. Put =COUNTIF($C$2:$C$9,G2) in H2, then fill down to H4. The expected answers are 3, 3, and 2. The dollar signs keep the status range fixed while the criterion cell changes as you copy the formula.
For a one-off number, =COUNTIF(C2:C9,"Done") returns 3. The criteria text must match the intended status. Excel’s comparisons are generally not case-sensitive, but inconsistent wording such as Complete versus Done still creates separate categories. For a shared tracker, use an approved list of statuses rather than relying on everyone to type the same words.
Our related guide explains how to create an Excel drop-down list that prevents status entry errors. Standardized input makes subsequent COUNTIF summaries more trustworthy.
Example 2: Count one owner and one status with COUNTIFS
To count Maya’s completed tasks, enter =COUNTIFS(B2:B9,"Maya",C2:C9,"Done"). The result is 1 because only T-106 meets both conditions. To count Leo’s open requests, replace Maya with Leo and Done with Open; the result is also 1, from T-105.
For a reusable report, put the owner in G7 and the status in H7, then use =COUNTIFS($B$2:$B$9,G7,$C$2:$C$9,H7). You can change those two inputs without editing the formula. This is an AND condition: a row must satisfy the owner and the status at the same time.
All criteria ranges must cover the same number of rows and columns. Mixing B2:B9 with C2:C10 produces a range-size error rather than a dependable count. If you add rows to the tracker, update every criteria range together or convert the source to an Excel Table with structured references.
Example 3: Count high-priority open requests
Use =COUNTIFS(C2:C9,"Open",E2:E9,"High") to count requests that are both open and high priority. The expected answer is 2: T-101 and T-105. T-103 and T-108 are high priority too, but they are Blocked, so they do not belong in the Open count.
This distinction matters when managers ask for an actionable queue. A total of four high-priority items is not the same as two high-priority items still marked Open. Write the precise question above each KPI and keep the source status definitions visible. If Blocked items also require escalation, report them separately rather than silently redefining Open.
Example 4: Count requests due within a date range
Suppose you need the number of Done requests due from 22 October through 24 October 2026, inclusive. Use =COUNTIFS(C2:C9,"Done",D2:D9,">="&DATE(2026,10,22),D2:D9,"<"&DATE(2026,10,25)). The result is 2: T-104 and T-106. The upper boundary is the following day, which also works when a source column contains date-and-time values.
For a rolling report, place the start date in G10 and the exclusive end date in H10, then use =COUNTIFS(C2:C9,"Done",D2:D9,">="&G10,D2:D9,"<"&H10). Keep the input dates visible and label the end date as exclusive. Avoid typing date comparisons as ambiguous strings such as “>=10/22/26” when coworkers use different regional formats.
Dates must be numeric Excel date values. A cell can look like 2026-10-22 while actually containing text imported from another system. If a date count unexpectedly returns zero, inspect the underlying data type before changing the formula. The same principle applies when preparing messy source data for an Excel PivotTable.
Example 5: Count overdue work without counting completed tasks
To count requests that are Open and due before a chosen reporting date, use =COUNTIFS(C2:C9,"Open",D2:D9,"<"&DATE(2026,10,27)). On the sample tracker the result is 3. The reporting date is a fixed illustration; for a live daily sheet, replace DATE(2026,10,27) with TODAY().
Do not use =COUNTIF(D2:D9,"<"&TODAY()) alone for an overdue-work KPI: it would also count old dates on Done rows. Likewise, a task with an empty due date needs its own missing-date flag rather than being silently treated as on time. Agree on whether Blocked work belongs in a separate escalation count.
How do you count either of two statuses?
COUNTIFS combines conditions with AND. To count Open OR Blocked requests in the same status column, add two nonoverlapping COUNTIF results: =COUNTIF(C2:C9,"Open")+COUNTIF(C2:C9,"Blocked"). This returns 5 in the sample. It works because a cell cannot simultaneously contain both exact status labels.
Do not blindly add overlapping criteria. For example, counting names containing “Ann” and names containing “Anna” can count Anna twice. When criteria overlap, define a mutually exclusive classification or use a different reporting method. For large summaries by several dimensions, a PivotTable with explicit row and filter fields may be easier to audit than dozens of separate formulas.
Why does COUNTIF return zero or the wrong total?
First, inspect the actual cells. Extra spaces, alternative spellings, pasted labels, and dates stored as text are common causes of a count that looks plausible but is wrong. Test a single known record against the intended criterion. Then confirm that the formula covers all data rows and does not include a totals row or unrelated header.
Second, check the operators and cell references. For a threshold held in G12, write =COUNTIF(F2:F9,">"&G12), not a quoted literal that says G12. Use quotation marks around text criteria and concatenate operators with referenced values. The Microsoft COUNTIFS reference also documents wildcard matching with * and ?; escape a literal wildcard with a tilde.
Third, distinguish counts from lookups. COUNTIFS tells you how many rows match; it does not return the owner, price, or record associated with a match. If you need a value from a corresponding row, see our worked XLOOKUP examples for office worksheets. Choose the function that answers the actual reporting question.
Build a reusable status summary without hiding source problems
Keep the raw task table on one worksheet and the summary on another. Label each metric precisely: Open requests, Done requests, High-priority Open, and Open overdue as of the reporting date. Place the reporting date in a visible input cell, and document whether missing due dates are excluded. This makes the same formula usable by a colleague next week.
For a basic completeness check, compare =COUNTA(A2:A9) with the sum of the three status counts. Both should be 8 for the sample. A difference suggests a blank status, an unapproved label, or an omitted row. Matching totals are useful but not proof that every owner, due date, or request ID is correct.
If the report feeds a handoff or management decision, retain the original export and record material corrections. Do not replace a zero with a guessed figure merely because a dashboard looks empty. Formula results should be reproducible from the underlying rows.
Conclusion: choose the count that matches your question
Use COUNTIF for one condition and COUNTIFS for several conditions that must all be true. Start with a small known dataset, keep ranges aligned, use real Excel dates, and check the count against source rows. Those habits turn quick spreadsheet formulas into a status report colleagues can understand and maintain.
Optional formula practice: If you want structured exercises for diagnosing spreadsheet formulas, see the Excel Formula Troubleshooter. It includes 14 practice exercises and 20 formula patterns; check the kit details to see whether they fit your needs.