COUNTIF and COUNTIFS in Excel: count according to your criteria

Counting cells based on conditions is a skill that seems simple at first, but quickly becomes crucial as soon as you work with real tables: sales, customer lists, due dates, or inventories. The functions COUNTIF and COUNTIFS provide a direct answer: the first for one criterion, the second for several. Here I explain their syntax, their concrete use cases, common pitfalls — and I give you formulas to copy-paste to save time.

In brief

Fundamental difference and syntax

COUNTIF: one criterion, one range

COUNTIF answers this question: “how many cells in this range match this criterion?” Its syntax is simple: =COUNTIF(range, criterion). The criterion can be exact text (“Red”), a logical operator combined with a number (“>=100”), or contain wildcards (* and ?) for partial matches.

COUNTIFS: multiple criteria

COUNTIFS extends the principle to multiple range/criterion pairs: =COUNTIFS(range_criterion1, criterion1, range_criterion2, criterion2, …). Each range must have the same number of rows/columns. The function returns the number of rows where all criteria are simultaneously true.

Summary table

Function	Usage	Example	Practical note
COUNTIF	1 criterion on 1 range	=COUNTIF(B2:B100, “Sold”)	Faster for a quick check
COUNTIFS	Multiple criteria on independent ranges	=COUNTIFS(B2:B100, “Sold”, C2:C100, “France”)	Ranges must be the same size
SUMPRODUCT	Alternative for arbitrary logic	=SUMPRODUCT((B2:B100=”Sold”)*(C2:C100=”France”))	More flexible, but sometimes slower

Concrete examples and useful variants

1) Counting with wildcards (partial search)

If you want to count customers whose name starts with “Dup”, use:

=COUNTIF(A2:A500, “Dup*”)

The wildcard * replaces any sequence of characters; ? replaces one character. COUNTIF is case-insensitive: “dupont” and “Dupont” will be counted the same way.

2) Numeric criteria and dates

To count amounts greater than 1000:

=COUNTIF(D2:D1000, “>1000”)

For a date after January 1, 2024:

=COUNTIF(E2:E100, “>2024-01-01”)

If Excel does not interpret the date correctly, encapsulate it in the DATE function or use a reference cell:

=COUNTIF(E2:E100, “>” & F1)

where F1 contains the date.

3) Multiple criteria: sales example

Number of validated orders in France for the sales representative “Martin”:

=COUNTIFS(StatusRange, “Validated”, CountryRange, “France”, SalesRepRange, “Martin”)

Make sure that StatusRange, CountryRange, and SalesRepRange cover exactly the same rows (e.g. B2:B500, C2:C500, D2:D500).

4) Advanced case: combining partial text and numbers

You are looking for rows where the product contains “USB” and the quantity is greater than 10:

=COUNTIFS(ProductRange, “*USB*”, QuantityRange, “>10”)

The first condition uses a wildcard, the second a numeric operator. This combination is often used to filter sales by product type and quantity threshold.

Common pitfalls and fixes

• Incorrect format: dates and numbers stored as text are not counted correctly. Solution: use VALUE, DATEVALUE, or force the format via paste special.
• Invisible spaces: a trailing space prevents exact matching. Clean with TRIM() or FIND/REPLACE.
• Different range sizes: COUNTIFS will return an error if the ranges do not have the same dimension.
• Dynamic criteria: concatenate the criterion with & (ampersand) to compare to a cell:
  

  =COUNTIF(A2:A100, “>” & G1)

• Performance: very large ranges with many criteria can slow down; SUMPRODUCT can be even slower depending on context.

Advanced alternatives and tips

SUMPRODUCT allows building arbitrary logic, for example complex ORs or weightings. To count rows where at least one condition is true, you cannot use COUNTIFS directly; you can combine COUNTIF or use SUMPRODUCT:

=SUMPRODUCT(((A2:A100=”X”) + (B2:B100=”Y”))>0)

This formula adds boolean conditions and returns 1 if at least one is true. It is more verbose but very powerful for ad hoc analyses.

Practical tip: after importing data

After importing a table — for example a CSV file — immediately check:

• column formats one by one (text vs number vs date),
• empty or duplicate cells at the end of the range,
• decimal separators that change depending on region (comma vs dot).

These checks avoid most false negatives when using COUNTIF and COUNTIFS. Once formats are standardized, your formulas become robust and reusable.

Ready-to-copy examples

• Count returned products:
  

  =COUNTIF(StatusRange, “Returned”)

• VIP clients in 2024:
  

  =COUNTIFS(ClientTypeRange, “VIP”, OrderDateRange, “>=2024-01-01”)

• French sales + amount > 5000:
  

  =COUNTIFS(CountryRange, “France”, AmountRange, “>5000”)

FAQ

Why does COUNTIFS return #VALUE! ?

The most common cause is that the ranges do not have the same size. Check that each range starts and ends on the same rows/columns. Another source: a badly formed criterion (e.g. “=” alone) or an incompatible type (date as text).

How to handle uppercase/lowercase?

COUNTIF and COUNTIFS are case-insensitive. If you need a case-sensitive comparison, you must use array formulas or SUMPRODUCT by combining EXACT() with N().

Can regular expressions be used?

Native Excel does not offer regular expressions in COUNTIF. For complex patterns, use Power Query or VBA, or advanced functions in Office 365 (LET, FILTER) combined with conditional logic.

How to debug a count that seems incorrect?

Proceed step by step: check the visible range (sorting/filtering), clean spaces and formats, test a simple criterion then add more progressively. Use conditional formatting to visualize what Excel considers true.

{
“@context”: “https://schema.org”,
“@type”: “WebPage”,
“about”: {
“@type”: “Thing”,
“name”: “Conditional counting with COUNTIF and COUNTIFS in Excel”
},
“keywords”: [“COUNTIF”, “COUNTIFS”, “Excel”, “count”, “criteria”]
}