Excel formula builder
COUNTIFS Formula Builder
Count rows that match one, two, or three criteria using deterministic COUNTIFS formula construction.
Builder inputs
Add criteria range and criteria value pairs. 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 |
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
Are these formulas generated with AI?
No. The builder uses deterministic TypeScript functions in the browser and does not call any AI API.
Do I need to upload my spreadsheet?
No. Enter ranges and criteria manually. The site does not upload, store, or process spreadsheet files.
Can I copy the generated formula?
Yes. Each output includes a copy button so you can paste the formula into Excel or Google Sheets.