Suppose we have a large Excel worksheet containing various data in different formats. We need to count the number of cells that end with a specific character or set of characters. This can be a tedious task if done manually. For instance, consider a situation where we have a column of email addresses, and we need to count the number of email addresses that end with “.com”. It would be a cumbersome task to manually count the number of email addresses that meet this criterion.
Excel Function for Counting Cells
Excel provides various functions to count the number of cells that meet a specific criterion. One of the functions that we can use to count the number of cells that end with a specific character or set of characters is the COUNTIF function. This function allows us to count the number of cells in a range that meet a specific criterion.
Syntax of COUNTIF Function
The syntax of the COUNTIF function is as follows:
=COUNTIF(range, criteria)Here, range refers to the range of cells that we want to count, and criteria is the criterion that we want to apply.
Using COUNTIF Function to Count Cells
We can use the COUNTIF function to count the number of cells that end with a specific character or set of characters. To do this, we need to use a wildcard character “”. The wildcard character “” represents any number of characters. We can use this wildcard character in the criteria of the COUNTIF function to count the number of cells that end with a specific character or set of characters.
Suppose we have a column of email addresses in column A, and we want to count the number of email addresses that end with “.com”. We can use the following formula:
=COUNTIF(A:A, "*com")Here, A:A refers to the range of cells in column A, and *com is the criteria that we want to apply. The * before “com” represents any number of characters before “com”. This formula will count the number of cells in column A that end with “.com”.
Examples
Let’s consider a few examples to understand how to use the COUNTIF function to count the number of cells that end with a specific character or set of characters.
Example 1
Suppose we have a column of names in column A, and we want to count the number of names that end with the letter “n”. We can use the following formula:
=COUNTIF(A:A, "*n")This formula will count the number of cells in column A that end with the letter “n”.
Example 2
Suppose we have a column of phone numbers in column A, and we want to count the number of phone numbers that end with “0”. We can use the following formula:
=COUNTIF(A:A, "*0")This formula will count the number of cells in column A that end with the digit “0”.
Example 3
Suppose we have a column of website addresses in column A, and we want to count the number of website addresses that end with “.org”. We can use the following formula:
=COUNTIF(A:A, "*.org")This formula will count the number of cells in column A that end with “.org”.
Thank you.
