Criteria are text, wildcards work, cells need &

The criterion is a piece of text

Lesson 4.1 counted provinces with COUNTIF(range, criterion). The criterion there was a cell holding "ON". The part that trips people is that a comparison is also text: ">400" in quotes, operator and number together. COUNTIFS — the plural — extends the idea to several conditions, each as a range, criterion pair, and a row is counted only when every pair holds.

Worked example — Ontario orders over $400

With prov in B and amount in D:

=COUNTIFS($B$2:$B$13, "ON", $D$2:$D$13, ">400")

→ 3 (orders 1001, 1003, 1006 — the same three Lesson 5.5 flagged). The pairs are read left to right: this range, this test; that range, that test. Every range is absolute because the formula will be copied to other cells with other criteria, and each pair's range must be the same height.

Now suppose the threshold lives in H1 so a manager can change it without editing formulas. ">H1" counts nothing — it is the literal text greater than H1. The operator and the cell must be joined with &:

=COUNTIF($D$2:$D$13, ">"&H1)

With 500 in H1 that is ">500", and the count is 5. The same joining works for a text criterion ("="&H2), a date (">="&DATE(2025,2,1)) and inside SUMIFS (next lesson).

Wildcards

A criterion can contain * (any run of characters, including none) and ? (exactly one character). In the products sheet, with product names in I2:I4 (Ledger binder, Desk lamp, Office chair):

=COUNTIF(I2:I4, "*lamp*")

→ 1. "D*" would count names starting with D; "A?00" matches A100 but not A1000. Matching is case-insensitive, as every spreadsheet text comparison is: "*LAMP*" also counts the Desk lamp.

The R side: sum() of a logical, and grepl()

R has no COUNTIF. A comparison produces a logical vector, and sum() counts its TRUEs — & joins the conditions exactly as COUNTIFS pairs them:

> sum(orders$prov == "ON" & orders$amount > 400)
[1] 3
> sum(orders$amount > 500)
[1] 5

Wildcards do not exist in ==: products$product == "*lamp*" compares against the literal seven-character string and is FALSE everywhere. Pattern matching is grepl(pattern, x), which returns TRUE where the pattern appears anywhere in the string — a substring test, so no * is needed — and is case-sensitive unless told otherwise:

> grepl("lamp", products$product)
[1] FALSE  TRUE FALSE
> grepl("Lamp", products$product)
[1] FALSE FALSE FALSE
> sum(grepl("lamp", products$product, ignore.case = TRUE))
[1] 1

The ignore.case = TRUE argument is what makes grepl behave like a spreadsheet wildcard count.

Type three, then answer the count

Type the COUNTIFS, the &-joined threshold and the R sum. The numeric item asks for the Ontario-over-$400 count.

CRISP-DM: a conditional count is data understanding → explore data; the helper cell in H1 is the beginning of a dashboard control (Module 14).