The COUNTIFS function in Google Sheets is a powerful tool for performing conditional counting within data sets. It enables users to count the number of cells that align with multiple criteria across various ranges, making it ideal for data analysis and reporting tasks where specific conditions must be met.

Syntax

COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])

criteria_range1: The first range of cells to evaluate against the first criterion.
criterion1: The condition that defines which cells in criteria_range1 will be counted.
criteria_range2 (optional): The second range of cells to evaluate against the second criterion.
criterion2 (optional): The condition that defines which cells in criteria_range2 will be counted.

Example #1

COUNTIFS(A2:A10, "<20", B2:B10, "Yes")

This function counts the number of rows where the value in column A is less than 20 and the corresponding value in column B is "Yes". For instance, if A2:A10 contains values like 15, 22, 18, and so forth, and B2:B10 contains "Yes", "No", "Yes", etc., the count could yield a result of 2.

Example #2

COUNTIFS(C2:C10, "Completed", D2:D10, ">=10")

This function counts the number of rows where the status in column C is "Completed" and the value in column D is greater than or equal to 10. If C2:C10 includes statuses like "Completed", "Pending", and column D has numbers like 5, 10, 12, etc., the function might return a count of 3.

Example #3

COUNTIFS(E2:E10, "<300", F2:F10, "<>" & "")

This function counts the number of rows where the values in column E are less than 300 and the corresponding cells in column F are not empty. If E2:E10 has values like 250, 400, and F2:F10 has data including values and blanks, it may return a count of 4.

Error handling

• VALUE!: This error occurs when the ranges provided have incompatible sizes. Ensure that all ranges are of equal length.
• N/A: This error may appear when the criteria specified cannot be matched. Check your criteria to confirm they are defined correctly.
• REF!: This error happens when a reference provided falls out of the valid range. Ensure that all ranges point to existing cells.

Conclusion

The COUNTIFS function is an essential capability in Google Sheets for users needing to analyze data based on multiple conditions. Its flexibility allows for effective data management and insightful reporting, making it an invaluable tool for data analysts and everyday spreadsheet users alike.