What is the COUNTIF function?
The COUNTIF function is a handy Excel function that is used to count the number of cells that meet particular criteria given. It can be used to count cells with text, dates, numbers, and that match specific criteria. The COUNTIF function has support for logical operators (<,>, =) and even wildcards (*,?) for partial matching.
In this post, however, the COUNTIF function will be used to count the number of cells that have words that end with specific text and the “*” is a wildcard matching any number of characters.
- Range (rng) – This is the range of cells that would be included in the count. e.g. C3:C13 (which is a column of data)
- Criteria (*txt) – This is the criteria that control what cells would be counted. e.g. “*d”
The return value given by this equation is a number representing the cells counted that met the criteria.
How to use the COUNTIF function to count cells that end with.
In this example, we would be using the COUNTIF function to count cells that end with a certain letter.
The following steps should be taken;
- Open the Microsoft Excel spreadsheet with the raw data you would be working with. This sheet already contains the data range and criteria on a separate table where the count would be retrieved
Figure 1. Sample data Count cells that end with
- Let’s start by finding the number of cells that end with “d”. Click on the cell where this count would be inputted (G3).
Figure 2. Click the cell that would hold the result
- Insert the following formula into the cell;
Figure 3. Insert formula into the cell
- Press Enter on your keyboard. The result should be displayed in the appropriate cell.
Figure 4. Press ENTER to view Count
- To count the cells that end with “t”, use
=COUNTIF(C3:C12,"*t")and press Enter.
Figure 5. Count cells that end with T
- To count the cells that end with “r”, use
=COUNTIF(C3:C12,"*r")and press Enter.
Figure 6. Count cells that end with R
- A very common mistake is forgetting to use apostrophes (“”) and even wildcards (*). You will get errors if you don’t use those characters appropriately in the formula.