How to count text with cells in Excel

Excel's COUNTIF function may count the number of cells in a range that satisfy a single condition. Dates, numbers, and text may all be counted in cells with COUNTIF. The COUNTIF criteria allow for partial matching using wildcards (*,?) and logical operators (,=).

COUNTIFS with criteria: What is it?

Simply put, there are occasions when working with table values when we need to count the values that finish in a particular text or pattern. For instance, let's say we want to know how many IDs there are that finish in a particular pattern. The operators were used to execute the formula's criteria. Let's look at the formula and an example to see how it works.

generic formula with text that is hardcoded:

= COUNTIF (range, *text*) 

range: range of values

*: a wildcard that locates any character

Look for the provided text or pattern. Use the text in the formula directly, or use the alternative formula below if you are using a cell reference for the text.

Formula general with text in the reference cell:

= COUNTIF ( range, * & A1 & * )

A1 denotes the text in A1 cell while & operator concatenate to *text*

Example: It could be difficult to grasp all of these. Let's try out this formula by applying it to the example below. We need to locate the IDs that finish in the A category from the ID data we have here.

The asterisk (*) wildcard will be used in the calculation to obtain the A category IDs in this case. Any amount of characters inside a lookup value are found using the wildcard * (asterisk).

Use this equation:

= COUNTIF ( C3:C14, *A )

OR

= COUNTIF ( C3:C14, * & E4 )

Explanation:

1.The COUNTIF function counts the cells in the specified range.

2.The * (asterisk) wildcard is used as a search criterion to find values with any number of characters.

3.& operator concatenates the two values where * is present. When E4 is used as the criteria, the formula only returns the cells that meet the criteria.

Here, the pattern is referenced as a cell, and the range is referenced as an array. To obtain the count, press Enter.

As you can see, the sum of the values that ends with value A equals 4.

Use the drag-down menu to copy the formula into more cells, or press Ctrl + D to quickly do so, as seen below.

As you can see, there is a count of all the various category IDs.

Using the excel filter option, you may determine which values in a range end with a certain pattern. Click the arrow button that appears after applying the filter to the ID header. The steps are listed below.

Steps:

1.Select the cell with the ID header. Applying a filter quickly Shift + Ctrl + L

2.Select the filter choice by clicking the arrow that showed up.

3.Uncheck the Select All checkbox.

4.Now enter a search using the wildcard * (asterisk) in the search field.

5.Enter *A in the search box and either choose the necessary values or, like in the gif above, choose (Select All Search Results).

alternative COUNTIF formula using the wildcard asterisk (*).

This formula will be used to count each cell in the range A1:A10 that contains the word ExcelTip:

=COUNTIF(A1:A10,”*ExcelTip*”)

Write the formula below to add up all the cells that begin with A.

=COUNTIF(A1:A10,”a*”)

Write this COUNTIF formula in a cell that ends in etc to count that cell.

=COUNTIF(A1:A10,”*etc”)

because COUNTIF does not care about case. It counts every cell that has the specified text in it, regardless of case.

Here are all the observational notes created with the Excel algorithm.

Notes:

Operators like equals to (=), less than equal to (=), larger than (>), and not equals to (>) can be used within functions used with numbers alone. The formula only works with numbers and text.

Did you learn about how do we count cells with text in Excel? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets and PowerPoint Slides.

You can also download WPS Office to edit the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience