Share this article:

The COUNTIF statement is a powerful function in Excel that can be used to count the number of cells in a range that meet a specified criterion. In this article, we will discuss five examples of using the COUNTIF statement in Excel, along with the data used and the result.

Example 1: Counting the Number of Sales Over a Certain Amount

Suppose you have a list of sales data that includes the sales amount and the salesperson who made the sale. You want to count the number of sales that are over $10,000.

SalespersonSales
John5000
Sarah12500
Tom9800
Emma7500
John12000
Sarah3000

To count the number of sales that are over $10,000, you can use the following formula:

=COUNTIF(B2:B7,”>10000″)

This formula counts the number of cells in the range B2:B7 that are greater than $10,000. The result will be as follows:

Number of Sales Over $10,000
3

Example 2: Counting the Number of Cells with a Certain Text

Suppose you have a list of employees and their job titles. You want to count the number of employees with the job title “Manager.”

Employee NameJob Title
JohnManager
SarahSales
TomManager
EmmaManager
DavidIT
MarySales

To count the number of employees with the job title “Manager,” you can use the following formula:

=COUNTIF(B2:B7,”Manager”)

This formula counts the number of cells in the range B2:B7 that contain the text “Manager.” The result will be as follows:

Number of Managers
3

Example 3: Counting the Number of Cells with a Certain Color

Suppose you have a list of students and their test scores. You have highlighted the cells with scores over 90 in green and you want to count the number of cells with green background color.

Student NameTest Score
John75
Sarah95
Tom85
Emma60

To count the number of cells with a green background color, you can use the following formula:

=COUNTIF(B2:B5,”=CELL(“color”,B2)>0″)

This formula counts the number of cells in the range B2:B5 that have a green background color. The result will be as follows:

Number of Cells with Green Background Color
1

Example 4: Counting the Number of Cells with a Certain Date

Suppose you have a list of appointments and their dates. You want to count the number of appointments that are scheduled for a particular date.

AppointmentDate
Meeting 13/1/2023
Meeting 23/2/2023
Meeting 33/1/2023
Meeting 43/4/2023
Meeting 53/1/2023

To count the number of appointments scheduled for March 1, 2023, you can use the following formula:

=COUNTIF(B2:B6,”3/1/

/2023″)

This formula counts the number of cells in the range B2:B6 that contain the date March 1, 2023. The result will be as follows:

Number of Appointments on 3/1/2023
3

Example 5: Counting the Number of Cells that Meet Multiple Criteria

Suppose you have a list of orders that includes the order number, the date the order was placed, and the salesperson who made the sale. You want to count the number of orders that were placed by John after March 1, 2023.

Order NumberOrder DateSalesperson
10013/2/2023John
10023/3/2023Sarah
10033/4/2023John
10043/5/2023Tom
10053/6/2023John

To count the number of orders that were placed by John after March 1, 2023, you can use the following formula:

=COUNTIFS(B2:B6,”>3/1/2023″,C2:C6,”John”)

This formula counts the number of cells in the range B2:B6 that contain a date after March 1, 2023, and the number of cells in the range C2:C6 that contain the name “John.” The result will be as follows:

Number of Orders Placed by John After 3/1/2023
2

Conclusion

The COUNTIF statement is a powerful function in Excel that can be used to count the number of cells in a range that meet a specified criterion. By using the COUNTIF statement, you can perform a wide range of tasks in Excel, from counting the number of sales over a certain amount to counting the number of cells with a certain color. With these examples, you can get started with using the COUNTIF statement in your own Excel spreadsheets.