COUNTIFS in Excel: Multiple Criteria Guide & Builder

Master COUNTIFS: Count with Multiple Conditions

The COUNTIFS function counts rows that meet multiple criteria across different columns. It is the multi-condition version of COUNTIF and one of the most versatile functions for data analysis in Excel and Google Sheets.

Step-by-Step: Using COUNTIFS for the First Time

Let us walk through a concrete example. Imagine you have a sales table:

• Column A: Region (East, West, North)
• Column B: Sales Amount (e.g. 250, 750, 1200)
• Column C: Status (Completed, Pending)

Goal: Count how many "Completed" orders in the "East" region exceeded $500.

Each pair (range, criteria) adds one filter. Only rows matching ALL three conditions are counted.

COUNTIF vs COUNTIFS: What is the difference?

COUNTIF handles one condition only. COUNTIFS handles two or more conditions simultaneously. Every condition is connected by AND logic—all conditions must be true for a row to be counted.

Real-World Use Cases

• Sales Reporting: Count orders where region = "East" AND amount > $500.
• HR Analytics: Count employees in department "Engineering" with tenure > 3 years.
• Inventory: Count products in category "Electronics" where stock < 10 (needs restocking).
• Education: Count students who scored above 80 in Math AND above 70 in Science.
• Customer Support: Count tickets tagged "Urgent" that are still "Open" and overdue by 3+ days.

Working with Date Ranges

To count rows where a date falls within a specific range, use two conditions on the same date column:

=COUNTIFS(A:A, ">="&DATE(2026,1,1), A:A, "<="&DATE(2026,3,31))

For dynamic dates stored in cells, reference them directly:

=COUNTIFS(A:A, ">="&D1, A:A, "<="&E1)

Advanced: COUNTIFS with OR Logic

COUNTIFS uses AND logic by default. To implement OR, add multiple COUNTIFS together:

=COUNTIFS(A:A, "East", B:B, ">500") + COUNTIFS(A:A, "West", B:B, ">500")

This counts rows where (Region = East AND Sales > 500) OR (Region = West AND Sales > 500).

Common Errors & Troubleshooting

• COUNTIFS returns 0: Likely causes: range sizes mismatch, text criteria not in quotes, or no data matches all conditions simultaneously.
• #VALUE! error: Usually means criteria ranges have different dimensions (e.g. A1:A100 paired with B1:B99). Ensure every range has the same row count.
• Counting blank cells: Use =COUNTIFS(A:A, "") for truly empty cells. Use =COUNTIFS(A:A, "") + COUNTIFS(A:A, "* ") to include blank-looking cells with only spaces.
• Cell contains numbers as text: If your criteria range stores numbers as text, use TEXT function: =COUNTIFS(A:A, TEXT(500, "0")).