COUNTIFS with Multiple Criteria — Count Rows That Match 2+ Conditions | SheetMaster

Counts cells that meet multiple criteria. Essential for data analysis with complex conditions.

Generated Formula
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2)

Learning Resources

Want to master Excel? Check out this Top-Rated Course.

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.

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.

FeatureCOUNTIFCOUNTIFS
ConditionsSingle conditionMultiple conditions (AND)
Syntax=COUNTIF(range, criteria)=COUNTIFS(range1, criteria1, range2, criteria2)
OR LogicNot supported nativelyRequires multiple formulas

Real-World Examples

  • 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).
  • Date Range: Count transactions between Jan 1 and Mar 31, 2026.

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))

Important: All criteria ranges must be the same size. If range1 is A1:A100, range2 must also be 100 rows. Mismatched sizes cause incorrect counts.

Common Errors & Fixes

  • COUNTIFS returns 0 or unexpected count

    Causes:
    • Criteria ranges have different lengths.
    • Text criteria not in quotes; number criteria wrong format.
    Fixes:
    • Use identical range sizes: A1:A100, B1:B100.
    • Use "Completed", ">100", "="&A1 for text, number, and cell reference.

Frequently Asked Questions

COUNTIFS with multiple criteria — how does it work?

COUNTIFS counts rows only when ALL conditions are true (AND logic). Syntax: =COUNTIFS(range1, criteria1, range2, criteria2). Example: =COUNTIFS(A:A, ">100", B:B, "Yes") counts rows where A>100 AND B="Yes".

Why does COUNTIFS return 0?

All conditions must be met in the same row. Check that criteria ranges are the same size and that criteria match data types (text in quotes, numbers with ">50" etc).

COUNTIFS ranges must be same size — what happens if they're not?

If ranges have different sizes, COUNTIFS may return 0 or wrong results. Always use the same number of rows for every criteria_range. Example: use A1:A100 with B1:B100, not A1:A100 with B2:B101.

Can COUNTIFS count with OR logic?

COUNTIFS is AND logic only. For OR, add multiple COUNTIFS: =COUNTIFS(A:A,"X")+COUNTIFS(A:A,"Y"), or use SUMPRODUCT with (condition1)+(condition2).

How do I count rows where one column is blank and another is not?

Use criteria "" for blank and "<>" for non-blank: =COUNTIFS(A:A,"", B:B,"<>").

What is the difference between COUNTIF and COUNTIFS?

COUNTIF has one condition; COUNTIFS has multiple criteria_range/criteria pairs. Use COUNTIFS when you need two or more conditions at once.

Related Formulas

COUNTIFSUMIFSSUMIFAVERAGEIFIFS

Want to become an Excel Pro?

Stop searching for formulas. Master Excel in 30 days with this top-rated course.

Learn More