It's 4:47 PM on Friday. Your manager just asked for a consolidated headcount report by 5:00 — broken down by department, hire date range, and active status. You have three HR sheets open: Staff_Q1_2024.xlsx, Contractors.xlsx, and Terminations_Log.xlsx. You try COUNTIF on the first sheet — works fine for 'Sales' — but when you add the second condition ('Hire Date > 2024-01-01'), Excel returns zero. You check the dates. Format looks right. You retype the formula. Still zero. You glance at the clock. 4:51.
Quick Answer
COUNTIFS counts cells that meet multiple criteria across multiple ranges — but only if each criterion is applied to its own range, ranges are same size, and text criteria are wrapped in quotes while dates and numbers aren’t. The most common mistake? Using =COUNTIFS(A2:A100,"Sales",B2:B100,">1/1/2024") when B2:B100 contains Excel serial numbers (e.g., 45321), not text — so you must use >DATE(2024,1,1) or >45292.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Basic COUNTIFS with two criteria | Type =COUNTIFS(range1,criteria1,range2,criteria2) | Counting staff in one dept hired after a date | All ranges must be identical height/width; no array logic without Ctrl+Shift+Enter |
| COUNTIFS with wildcards (*, ?) | Use "*Sales*" or "A?" inside quotes | Partial text matches (e.g., "VP of Sales", "Sales Intern") | Case-insensitive only; no regex; * matches any number of chars |
| COUNTIFS with dates using DATE() | =COUNTIFS(B2:B100,">="&DATE(2024,3,1),B2:B100,"<="&DATE(2024,6,30)) | Quarterly reporting where dates change monthly | DATE() recalculates every time — can slow large sheets |
| COUNTIFS with cell references as criteria | =COUNTIFS(A2:A100,D1,B2:B100,">="&E1) | Dynamic dashboards where users change filters in D1/E1 | If E1 is blank, ">="&E1 becomes ">=", which counts all non-blanks — often unintended |
| COUNTIFS across sheets | =COUNTIFS('Q1 Staff'!A2:A100,"Sales",'Q1 Staff'!C2:C100,">0") | Consolidating from same-structure sheets | Sheet names with spaces require single quotes; broken links if sheet renamed |
| COUNTIFS + SUMPRODUCT for OR logic | =SUMPRODUCT((A2:A100="Sales")+(A2:A100="Marketing"),--(B2:B100>45292)) | Counting people in Sales OR Marketing hired after Jan 1 | Not native COUNTIFS — more complex, slower on 100k+ rows |
Method 1 Deep Dive
Let’s fix that Friday 4:47 problem. Here’s your actual data in Sheet1:
| A (Dept) | B (Hire Date) | C (Status) |
|---|---|---|
| Sales | 45321 | Active |
| Engineering | 45352 | Active |
| Sales | 45292 | On Leave |
| Marketing | 45383 | Active |
| Sales | 45412 | Active |
| Finance | 45261 | Terminated |
| Sales | 45305 | Active |
Note: Column B contains Excel serial numbers (45321 = March 12, 2024). Don’t type ">1/1/2024". That forces Excel to treat it as text. Instead:
- Select cell E1 → type
=COUNTIFS(A2:A8,"Sales",B2:B8,">="&DATE(2024,1,1),C2:C8,"Active") - Press Enter. Result: 3 (rows 1, 5, 7)
- To avoid DATE(), paste this faster version in E2:
=COUNTIFS(A2:A8,"Sales",B2:B8,">=45292",C2:C8,"Active")
The surprise? You can use raw serial numbers in criteria — and they’re faster than DATE(). Just make sure you know the number: =DATE(2024,1,1) returns 45292. Check with =B2 on any date cell to see its serial value.
Method 2 Deep Dive
Now imagine your manager says: “Give me headcount for anyone whose name starts with ‘S’ AND works in Sales OR Engineering.” Wildcards + OR logic — this is where most people give up and pivot to filters.
You’ll need two COUNTIFS formulas + addition — no nesting, no SUMPRODUCT unless you want volatility.
Assume your full list (A2:C12) includes names:
| A (Name) | B (Dept) | C (Hire Date) |
|---|---|---|
| Sarah Chen | Sales | 45321 |
| James Wu | Engineering | 45352 |
| Stella Park | Sales | 45292 |
| Mark Lee | Marketing | 45383 |
| Sam Rivera | Engineering | 45412 |
| Tina Zhao | Finance | 45261 |
| Simon Gupta | Sales | 45305 |
Formula in F1:
=COUNTIFS(A2:A8,"S*",B2:B8,"Sales") + COUNTIFS(A2:A8,"S*",B2:B8,"Engineering")
Returns 3: Sarah Chen, Stella Park, Sam Rivera.
Why not one COUNTIFS with {"Sales","Engineering"}? Because COUNTIFS doesn’t accept array constants in criteria — it treats them as a single string. You’ll get zero. Always split OR conditions into separate COUNTIFS calls and sum them.
Pro tip: Press Alt + = to auto-sum a range — but for COUNTIFS addition, type the first COUNTIFS, copy it (Ctrl+C), paste beside the + sign, then edit the dept reference. Saves 10 seconds per combo.
Cheat Sheet
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Start formula with =COUNTIFS( | Excel shows argument tooltip | None |
| 2 | Select first range (e.g., A2:A100) | Highlight appears | Shift+Space selects current region |
| 3 | Add first criterion in quotes if text ("Sales") or unquoted if number/date (>=45292) | No #VALUE! error | F9 on selected date cell shows serial number |
| 4 | Add second range/criterion pair — ranges must match size exactly | Correct count, not #N/A | Ctrl+` (grave) toggles formula view |
| 5 | For OR logic (Sales or Engineering), write two COUNTIFS + add | Accurate multi-dept totals | Alt+= inserts SUM() — but here, type + and paste |
| 6 | Test with =COUNTA(A2:A100) to verify row count matches your ranges | Catches mismatched range heights | Alt+M+V opens Evaluate Formula |
| 7 | Use "*text*" for partial matches, "?" for single char, "~*" to find literal asterisk | Finds "VP of Sales" and "Sales Intern" | Ctrl+H → ~* finds asterisks in data |
