Statistical

COUNTIFS Function in Excel

Counts cells that meet multiple criteria across multiple ranges.

Syntax

  • =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Arguments

  • criteria_range1 (required): First range to evaluate
  • criteria1 (required): Criteria for first range
  • criteria_range2 (optional): Additional range to evaluate
  • criteria2 (optional): Criteria for additional range

Examples

  • =COUNTIFS(A:A, "East", B:B, ">1000") - Count East region sales over $1000 - Result: 15
  • =COUNTIFS(A:A, A2, B:B, ">=2024-01-01", B:B, "<=2024-12-31") - Count by product in date range - Result: Count

Overview

  • COUNTIFS counts rows where every criteria pair is true. It mirrors SUMIFS logic but returns a count — ideal for volume KPIs next to dollar totals on the same dashboard.

Dashboard pairs (COUNTIFS + SUMIFS)

  • Orders count: =COUNTIFS(Region,"East",Status,"Closed").
  • Revenue sum: =SUMIFS(Amount,Region,"East",Status,"Closed").
  • Same pairs → comparable metrics.

Date-range COUNTIFS

  • =COUNTIFS(Date,">="&Start,Date,"<="&End,Status,"Open").
  • Store Start/End on a Setup sheet.
  • Confirm Date column is real serial dates.

Troubleshooting

  • 0 result → test each pair separately.
  • Compare [COUNTIF vs COUNTIFS](/compare/countif-vs-countifs/).
  • Dedupe workflow: [remove duplicates](/blog/remove-duplicates-in-excel/).

Quick answer: countifs multiple criteria

  • COUNTIFS counts rows where all criteria pairs match (AND logic). Each pair is criteria_range followed by criteria.
  • Add pairs for region AND product AND date window.
  • Confirm date columns are real Excel dates, not text.
  • Pair with SUMIFS on the same filters for value + volume KPIs.

COUNTIFS best practices

  • Use COUNTIFS when a count depends on two or more conditions.
  • Make every criteria_range the same size to avoid incorrect counts.
  • Use wildcard criteria like "*text*" when the target text can appear inside a longer value.

AND logic with criteria pairs

  • Each criteria_range and criteria pair adds another AND condition — all must be true for the row to count.
  • Open tasks for one owner: =COUNTIFS(Status,"Open",Owner,"Alex").
  • Due this month and not closed: =COUNTIFS(DueDate,">="&Start,DueDate,"<="&End,Status,"<>Closed").

Date-range COUNTIFS template

  • Put Start and End in named cells, then reference them in criteria with & concatenation.
  • Confirm DueDate values are real Excel dates, not text that looks like a date.
  • Combine with SUMIFS on the same dashboard for volume vs value KPIs.

People also ask

  • How many criteria can COUNTIFS use? — Up to 127 criteria pairs in modern Excel.
  • Why does COUNTIFS return 0? — One criterion fails; test each pair in isolation.

Common errors

  • #VALUE! when ranges have different sizes
  • Criteria must be in quotes if text/operators

Use cases

  • Multi-criteria counting
  • Data validation
  • Conditional analysis

Frequently asked questions

  • How is COUNTIFS different from COUNTIF? COUNTIF allows one condition. COUNTIFS requires all listed pairs to match (AND logic).
  • Can COUNTIFS do OR logic? Not in one formula — add two COUNTIFS and subtract overlap, or use FILTER in M365.
  • COUNTIFS with multiple regions? List each region as separate formulas and sum, or use criteria in cells with SUMPRODUCT patterns.
  • What is the difference between COUNTIF and COUNTIFS? COUNTIF handles one criterion, COUNTIFS handles multiple criteria with AND logic. =COUNTIFS(A:A,"East",B:B,">100") counts rows where A is East AND B>100. All criteria must be met.
  • How do I count with OR logic? Add multiple COUNTIFS: =COUNTIFS(A:A,"East")+COUNTIFS(A:A,"West") counts East OR West. For same column: =COUNTIF(A:A,"East")+COUNTIF(A:A,"West") or =SUMPRODUCT((A:A="East")+(A:A="West")).
  • Can COUNTIFS use wildcards? Yes! * matches any characters, ? matches single character. =COUNTIFS(A:A,"*phone*") counts cells containing 'phone'. =COUNTIFS(A:A,"???") counts 3-character entries.
  • How do I count between two values? Use two criteria on same range: =COUNTIFS(A:A,">10",A:A,"<20") counts values between 10 and 20. For dates: =COUNTIFS(A:A,">="&DATE(2024,1,1),A:A,"<="&DATE(2024,12,31)).
  • Why does COUNTIFS return 0 when matches exist? Check: 1) Ranges must be same size; 2) Numbers stored as text won't match numeric criteria; 3) Extra spaces in data; 4) Date format issues. Use TRIM and VALUE to clean data.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.