Excel formula builder
COUNTIFS Formula Builder
Count rows that match one, two, or three criteria with COUNTIFS. The builder runs in your browser and returns copyable Excel and Google Sheets formulas.
Builder inputs
Add criteria range and criteria value pairs. Keep ranges the same height; empty optional pairs are ignored.
=COUNTIFS(C2:C100, "Complete", B2:B100, "Maya")=COUNTIFS(C2:C100, "Complete", B2:B100, "Maya")COUNTIFS uses paired criteria range and criteria value arguments.How this formula works
- COUNTIFS counts rows only when every criteria pair is true.
- Text criteria are quoted automatically.
- Criteria can also use operators such as >=100 or <>Closed.
Best fit
Best for
- Counting matching rows by status, owner, category, month, or date range.
- Dashboard tiles such as completed tasks, overdue items, or transactions this month.
- Checking how many rows meet several criteria without adding a numeric amount column.
Not for
- Adding values from an amount column. Use SUMIFS when you need a total.
- Returning the actual rows. Use FILTER or QUERY when you need a filtered list.
- Counting distinct values. Use the Count Unique builder for unique names, IDs, or categories.
Useful formula variations
=COUNTIFS(C2:C100, "Complete")Use one criteria pair when you only need a simple status count.
=COUNTIFS(A2:A100, ">="&DATE(YEAR(F1),MONTH(F1),1), A2:A100, "<"&EOMONTH(F1,0)+1)Use this when column A contains real dates and F1 contains any date in the target month.
=COUNTIFS(A2:A100, ">="&F1, A2:A100, "<"&G1)Use an exclusive upper boundary when source rows can include time values.
Sample data
| Task | Owner | Status | Due Date |
|---|---|---|---|
| Import leads | Maya | Complete | 2026-01-06 |
| Clean headers | Nico | In Progress | 2026-01-08 |
| Review budget | Maya | Complete | 2026-01-12 |
| Publish report | Iris | Blocked | 2026-01-15 |
Returned counts from the sample data
| Criteria setup | Returned count | Rows included |
|---|---|---|
| Status = Complete and Owner = Maya | 2 | Import leads and Review budget. |
| Status = In Progress | 1 | Clean headers. |
| Due Date in January 2026 | 4 | All four sample tasks are due in January 2026. |
Builder field guide
| Field | Use it for | Example |
|---|---|---|
| Criteria range 1 | The first column to test | C2:C100 for Status |
| Criteria value 1 | The first required match | Complete or F2 |
| Criteria range 2 optional | A second column to test | B2:B100 for Owner |
| Criteria range 3 optional | Date, category, or priority checks | D2:D100 with ">=2026-01-01" |
Troubleshooting
| Problem | Likely cause | Fix |
|---|---|---|
| COUNTIFS returns 0 | The criteria does not match exact source values or the dates are stored as text. | Check source spelling, hidden spaces, and whether date cells are real dates. |
| #VALUE! appears | Criteria ranges do not have matching sizes. | Use ranges with the same number of rows, such as A2:A100 and C2:C100. |
| A count looks like a total | COUNTIFS counts rows and never adds amounts. | Switch to SUMIFS when you need to total the Amount column. |
Common mistakes
- Every criteria range must have the same number of rows.
- COUNTIFS is for row counts, not sums. Use SUMIFS when you need to add values.
- Blank criteria fields should be removed instead of leaving an empty range/value pair.
Related formulas
FAQ
Does this build Excel and Google Sheets formulas?
Yes. The COUNTIFS syntax generated here works in both Excel and Google Sheets for normal criteria pairs.
Why does COUNTIFS return #VALUE?
The most common cause is mismatched criteria range sizes. Use ranges with the same number of rows, such as B2:B100 and C2:C100.
Can I count rows by month with this builder?
Use the COUNTIFS by month variation when your date column contains real spreadsheet dates and your month selector is a real date cell.
Should I use COUNTIFS or SUMIFS?
Use COUNTIFS when you need a row count. Use SUMIFS when you need to add amounts, hours, revenue, or other numeric values.
Are these formulas generated with AI?
No. The builder uses built-in spreadsheet rules in your browser and does not call an AI service.
Do I need to upload my spreadsheet?
No. Enter ranges and criteria manually. The site does not upload, store, or process spreadsheet files.