While working with Excel, we are able to count values in a data set based on a given criteria by using the **COUNTIF **function. COUNTIF returns the number of values in a specified range based on a given condition. This step by step tutorial will assist all levels of Excel users in counting values that end with a specific text or character.

*Figure 1. Final result: Count cells that end with “er”*

Final formula: **=COUNTIF(B3:B7,"*er")**

**Syntax of COUNTIF Function**

`=`

**COUNTIF**(**range**,**criteria**)

Parameters:

**range**– the data range that will be evaluated using the criteria**criteria**– the criteria or condition that determines which cells will be counted

**Setting up Our Data**

Our table contains a list of Animals (column B). Cell E2 contains the criteria that we want to apply which is **cells ending with “er”**. In cell E3, the count based on the given criteria will be recorded.

* Figure 2. Sample data to count cells that end with “er”*

**Count cells that end with “er”**

In order to determine the number of cells that end with “er”, we follow these steps:

**Step 1**. Select cell E3

**Step 2**. Enter the formula: `=`

**COUNTIF**(**B3:B7**,"***er**")

**Step 3**: Press ENTER

*Figure 3. Using COUNTIF and asterisk “*” to count cells that end with “er”*

Our range for the COUNTIF function is the range **B3:B7** which contains our data for “Animals”. The criteria is “***er**” which means that we want to count the animals whose names end with “er”. The asterisk “*****” is a wildcard in Excel and it means that there could be any number of characters before “**er**”.

As a result, our formula returns **2**, which corresponds to two values that end with “er”: hamster and rooster.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment