When analysing a massive spreadsheet, you frequently need to know how many times a specific item appears. If you simply want to count how many times the word “Completed” appears in a column, the standard COUNTIF function works perfectly. However, data analysis is rarely that one-dimensional.
What if you need to know how many times a task is marked “Completed” and assigned to “John” and has a priority of “High”? When you need to count rows that meet two or more distinct conditions simultaneously, you must use the COUNTIFS function.
Understanding the COUNTIFS Syntax
The COUNTIFS function (with an “S” at the end) allows you to test up to 127 different pairs of conditions before Excel decides whether to count a row.
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
- criteria_range1: The first column of data you want to evaluate.
- criteria1: The specific condition that must be met in that first column.
- criteria_range2, criteria2: (Optional) The second column to evaluate, and its specific condition.
Every single condition must be true for a row to be counted. If even one condition fails, the row is ignored.
Example: Counting with Two Text Conditions
Imagine a project management spreadsheet:
- Column A contains the Employee Name (e.g., Sarah, John).
- Column B contains the Task Status (e.g., Pending, Completed).
You want to find out how many tasks “Sarah” currently has “Pending”.
Click an empty cell and type:
=COUNTIFS(A2:A100, "Sarah", B2:B100, "Pending")
How Excel processes this: It looks at row 2. Is Column A “Sarah”? If yes, it checks Column B. Is Column B “Pending”? If yes, it counts 1. It repeats this for every row down to 100.
Example: Using Logical Operators with Numbers
COUNTIFS is incredibly powerful when dealing with numbers and dates by using logical operators (>, <, >=, <=, <>).
Suppose you have a sales sheet:
- Column C contains the Salesperson’s Name.
- Column D contains the Sale Amount (in dollars).
You want to know how many times “David” made a sale greater than $500.
=COUNTIFS(C2:C100, "David", D2:D100, ">500")
Crucial Rule: Whenever you use a logical operator (like >) inside the formula, the operator and the number must be enclosed entirely in double quotation marks.
Using Cell References Instead of Hardcoding
Typing names and numbers directly into the formula (hardcoding) is inefficient because you have to rewrite the formula to check a different name. Instead, you should use cell references.
Type “David” into cell F1. Type “500” into cell G1. Now rewrite your formula to look at those cells:
=COUNTIFS(C2:C100, F1, D2:D100, ">"&G1)
Notice the syntax change here. Because F1 is a standard cell reference, it requires no quotation marks. However, when combining a logical operator (>) with a cell reference (G1), you must put the operator in quotes and use an ampersand (&) to join it to the cell reference. Now, you can change the name in F1 or the target amount in G1, and the count will update instantly.