While the standard COUNTIF function is excellent for simple tallies—such as counting how many times the word “Completed” appears in a column—it becomes entirely useless the moment your data analysis requires multiple conditions.
If you have an HR spreadsheet and need to know exactly how many employees are in the Engineering department AND have a tenure of more than 5 years AND are located in the London office, a single COUNTIF cannot help you.
To solve this, Google Sheets offers the COUNTIFS function (with an ‘S’ for plural). In this guide, you will learn how to build complex, multi-layered counting formulas.
The Basic COUNTIFS Syntax
Unlike SUMIFS, which requires you to specify a dedicated “sum range” first, COUNTIFS only cares about condition pairs. The syntax is simply a repeating chain of ranges and criteria:
=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])You can chain up to 127 different pairs of conditions in a single formula. Every single condition you specify must be TRUE for Google Sheets to count that specific row.
Use Case 1: Counting with Two Text Conditions
Let’s build the HR scenario mentioned above. Column B contains the Department, and Column C contains the Office Location.
We want to count how many Engineering employees are based in London.
=COUNTIFS(B2:B1000, "Engineering", C2:C1000, "London")Google Sheets will scan row 2. If Column B is Engineering, but Column C is New York, it skips the row. It only adds a “1” to the total tally if both columns match perfectly.
Use Case 2: Using Mathematical Operators
You can mix text criteria with mathematical evaluations. Suppose Column D contains the employee’s tenure in years.
We want to find Engineering employees in London who have been with the company for more than 5 years.
=COUNTIFS(B2:B1000, "Engineering", C2:C1000, "London", D2:D1000, ">5")Crucial Rule: When using mathematical operators like greater than (>), less than (<), or not equal to (<>) inside a COUNTIFS function, you must enclose both the operator and the number inside double quotation marks.
Use Case 3: Counting Between Two Dates
One of the most common uses for COUNTIFS is determining how many events occurred within a specific timeframe (e.g., how many sales occurred in Q3).
Suppose Column A contains the dates of the sales. You need to establish a floor (greater than or equal to July 1st) and a ceiling (less than or equal to September 30th) looking at the exact same column.
=COUNTIFS(A2:A1000, ">=7/1/2024", A2:A1000, "<=9/30/2024")Notice that we referenced A2:A1000 twice. This is perfectly legal and is the standard method for establishing date brackets.
By mastering the COUNTIFS function, you can instantly extract highly specific demographics and statistics from raw datasets, forming the backbone of any professional reporting dashboard.