The COUNTIF function in Google Sheets is one of the very useful formulas that is used for counting the number of cells that meets a given condition or criteria. This article covers the formula syntax and description of COUNTIF function and its practical application in different type of data.
Formula Syntax
Below is the formula syntax of COUNTIF function.
=COUNTIF(range, criterion)where;
Range: the range of cells to evaluate (for example A1:A15).
Criterion: The condition based on which cells should be counted (form example “>15”, “Completed”, “C1” cell reference)
Practical Applications
Example 1: Counting Products based on Condition
In below example, there is data of product wise sales quantity for the month of April 2025. Requirement is to count how many products has the sales quantity greater than 50 units. In this case you can use the COUNTIF function.
Formula and Result:
Formula in D4 Cell: =COUNTIF(B2:B9,">50")
Result: 3This formula counts the number of cells in B2:B9 range that has the quantity values greater than 50. If the condition is a single numeric value (for example 20), do not use the double quotes. If it is a text value or a number combined with mathematical operators, enclose with double quotations.
Example 2: Count Number of Present and Absent Days of Employees
Below example has attendance record of employees. Requirement is to count the number of present days and absent days. In this case you can use the COUNTIF function.
Formula and Result in AG3 and AH3 cell:
Formula in AG3: =COUNTIF(B3:AF3,"P")
Result: 24
Formula in AH3: =COUNTIF(B3:AF3,"A")
Result: 2Here, COUNTIF function counts the number of cells in B3:AF3 range where it finds the letter P. Which is the count of present days of first employee. Similarly, another formula counts the number of cells in B3:AF3 range where it finds the letter A. Which is the count of absent days of the employees.
Example 3: Count the Service Rating by Customers
Below example has the data of customer name and the ratings given by them for the service of an organization. Requirement is to count how many customers rated for which level.
Formula and Result:
Formula in E4 Cell: =COUNTIF($B$2:$B$15,D4)
Result: 3In this formula, COUNTIF function counted for number of cells that has the rating level “very good” in B2:B15 range. Result is returned as 3. In this formula, the cell reference D4 is used in the criterion parameter of the formula. So, if the criterion is written in a cell, you can give the cell reference where there is criterion written. The same formula can be used for other rating levels by using drag and drop or auto fill.
Conclusion
The COUNTIF function in Google Sheets is a versatile and essential tool for data analysis. It allows users to quickly count the number of cells that meet a specific condition, making it ideal for tasks like tracking attendance, analyzing sales, customer feedback analysis and more. Its simplicity and power make it especially useful for organizing large datasets and drawing meaningful insights with minimal effort. By mastering COUNTIF function, users can improve accuracy, save time, and make smarter, data-driven decisions in both personal and professional contexts.
