In Excel, which function would you use to count the number of cells in a range that meet a specific condition?

Question

In Excel, which function would you use to count the number of cells in a range that meet a specific condition?

1. A. =SUMIF(A1:A10, "10")
2. B. =COUNTBLANK(A1:A10)
3. C. =COUNT(A1:A10)
4. D. =COUNTIF(A1:A10, "10") (Correct answer)

Correct Answer

Option D — =COUNTIF(A1:A10, "10")

Detailed Solution & Explanation

The correct answer is =COUNTIF(A1:A10, ">10").

Key Points

• COUNTIF counts the cells in a range that satisfy a single condition, which is exactly what the question asks for.
• The syntax is =COUNTIF(range, criteria), with the criteria given in quotation marks when it contains an operator.
• The alternatives count or total something else:
  • SUMIF adds the values meeting a condition rather than counting them
  • COUNT counts cells containing numbers, with no condition
  • COUNTBLANK counts empty cells

Additional Information

• For several conditions at once, use COUNTIFS, SUMIFS and AVERAGEIFS, each taking range/criteria pairs.
• The related counting functions: COUNTA counts all non-empty cells including text, while COUNT is restricted to numbers.
• Criteria may use wildcards — ? for a single character and *** for any sequence — so `"A*"` counts entries beginning with A.
• Lookup functions commonly tested alongside these: VLOOKUP, HLOOKUP, INDEX with MATCH, and the newer XLOOKUP.