COUNTIF vs COUNTIFS

COUNTIF vs COUNTIFS

Compare COUNTIF and COUNTIFS for counting records by one condition or multiple conditions in Excel.

Quick verdict

  • Use COUNTIF for a single condition. Use COUNTIFS when the count depends on multiple filters.

How they differ

  • COUNTIF counts cells in one range that meet one condition.
  • COUNTIFS counts rows or records that satisfy multiple range and criteria pairs.

Practical examples

  • Use COUNTIF to count open tasks, specific names, or values above a threshold.
  • Use COUNTIFS to count open tasks assigned to a person and due this month.

Frequently asked questions

  • Can FILTER replace every VLOOKUP? No. FILTER is for multiple matching rows and requires Microsoft 365. VLOOKUP still fits single-value lookups and older Excel versions.
  • Why do I see #SPILL with FILTER but #N/A with VLOOKUP? Different errors — #SPILL means the output range is blocked; #N/A means no match was found. See our FILTER spill and #N/A guides.
  • Which is better for dropdown source lists? UNIQUE + SORT often beats both for dynamic lists. VLOOKUP returns one value; FILTER returns all matches.