Quick summary
Summarize this blog with AI
Introduction
R has no built-in Excel-style COUNTIF function. Form a logical condition and count its TRUE values with sum(). For multiple conditions use vectorized & or |, then choose how unknown values should affect the result. This lesson compares the same scalar and grouped counts in base R, dplyr and data.table.
Tested with R 4.5.1, dplyr 1.1.4 and data.table 1.18.6.1. Run the code blocks in order. Install the named packages before starting; inputs are supplied below.
Count one condition and show unknowns
Each row below is one order. A missing amount cannot be classified as above or below the threshold.
orders <- data.frame(region = c("East", "East", "West", "West", "West", "East"),
amount = c(100, 250, 300, NA, 50, 200),
paid = c(TRUE, TRUE, FALSE, TRUE, TRUE, NA))
above <- orders$amount > 200
print(above) # FALSE TRUE TRUE NA FALSE FALSE
stopifnot(is.na(sum(above)))
print(c(matched = sum(above, na.rm = TRUE), unknown = sum(is.na(above))))
# matched 2; unknown 1
stopifnot(sum(above, na.rm = TRUE) == 2L)
The result with na.rm is the count of known matches, not evidence that every order was inspected successfully. For equality, use ==; for an inclusive threshold, use >=.
Combine criteria without double-counting
COUNTIFS-style logic requires all conditions on the same row. An OR expression counts a row once even when both tests are true.
qualified <- orders$amount >= 200 & orders$paid
stopifnot(sum(qualified, na.rm = TRUE) == 1L)
stopifnot(sum(is.na(qualified)) == 2L)
east_or_large <- orders$region == "East" | orders$amount > 200
stopifnot(sum(east_or_large, na.rm = TRUE) == 4L)
stopifnot(sum(logical(0)) == 0L, sum(c(NA, NA), na.rm = TRUE) == 0L)
stopifnot(sum(orders$region == "North") == 0L)
Use & and | for elementwise conditions, not scalar && and ||. Comparisons do not automatically implement Excel wildcard syntax: use startsWith() for a literal prefix or a deliberately chosen regex for patterns.
Keep zero-count groups with summarise
Summarizing a condition preserves observed groups whose match count is zero. Filtering first can remove such groups entirely.
by_region <- orders |>
dplyr::group_by(region) |>
dplyr::summarise(matched = sum(amount > 200, na.rm = TRUE),
unknown = sum(is.na(amount)), .groups = "drop")
print(as.data.frame(by_region))
# region matched unknown
# East 1 0
# West 1 1
stopifnot(identical(by_region$matched, c(1L, 1L)))
only_large <- orders |> dplyr::filter(amount > 200)
stopifnot(nrow(only_large) == 2L)
stopifnot(nrow(orders[FALSE, ]) == 0L)
dplyr::n() takes no data argument; use it inside summarise/mutate for the current group size, or use nrow on a filtered data frame. It is not the final step of a pipe receiving a data frame.
Return a scalar total in data.table
The i expression filters rows; .N counts those rows. Adding by changes the output to a separate count for each group.
DT <- data.table::as.data.table(orders)
stopifnot(DT[amount > 200, .N] == 2L)
counts <- DT[, .(matched = sum(amount > 200, na.rm = TRUE)), by = region]
stopifnot(identical(counts$matched, c(1L, 1L)))
stopifnot(DT[amount > 1000, .N] == 0L)
stopifnot(DT[0L, .N] == 0L)
labels <- c("paid", "PAID", "unpaid", NA_character_)
stopifnot(sum(labels == "paid", na.rm = TRUE) == 1L)
Check type before comparing: character amounts sort lexically rather than numerically. Do not claim one package is faster for every workload; benchmark the actual operation only when performance matters.
