How To Count Cells With Text in Excel (Plus Tips)

By Indeed Editorial Team

Published April 8, 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

To organize and analyze data, professionals may use Excel to create spreadsheets and input their data. Sometimes, to analyze the data, an analyst may count cells with specific data, such as text. Learning how you can count cells with text in Excel provides you with an efficient method to review large sets of data and isolate information. In this article, we share steps on how to count cells with text in Excel and provide tips to help you better analyze data in this application.

Related: 11 of the Best Excel Alternative Tools for Data Analysis

When to count cells with text in Excel

When analyzing your data in a spreadsheet, you may require the ability to count only cells with text. You can find and isolate specific data by using the "COUNTIF" function in Excel. The "COUNTIF" function in Excel counts all cells containing text you input. With this function, you can count cells in a particular column or within the entire spreadsheet. This may be useful when analyzing customer data. For example, an analyst may count how many times a customer's name appears in the order history by using the "COUNTIF" function and inputting the customer's name.

Project managers may also find the ability to count cells with text useful when completing performance reviews for their team members. For example, if there is a shared spreadsheet that the team uses to sign up for project tasks, the manager can see which team members are volunteering for additional tasks. This function also helps perform data analysis tasks more efficiently because it can count data automatically rather than manually.

Related: How To Conduct Exploratory Data Analysis in 6 Steps

How to count cells with text in Excel

To count cells with text in Excel on your computer, you can follow these steps

1. Open Excel worksheet

You can open an Excel worksheet by finding the file in your documents folder and double-clicking on it. You can also click the search bar on the bottom tab of your computer screen and search for Excel. Once you open the application, go to "file" and click "open" so that you can start a new worksheet or open a worksheet that already has data in it.

2. Select an empty cell

Select any empty cell on the spreadsheet. You can do this by moving arrow keys to a cell without numbers or text or by moving your cursor to an empty cell and clicking on it. Often it's easy to choose an empty cell close to your data set. For example, if you have data in columns "A" through "D" and it extends down ten rows, then you may select empty cell "A11."

3. Input formula

In the empty cell, you can input the formula to count cells with text. The Excel formula for this function is "=COUNTIF (range, criteria)" without quotations. In this formula range is all the cells you want to include in your count and criteria is text.

4. Specify the range in which you're searching

To specify the range in which you're searching and counting text, look at the x- and y-axis on the top and left of the spreadsheet. The x-axis displays letters in alphabetical order, while the y-axis displays numbers. So a range may be "A1" through "B2" which is a range that includes "A1, A2, B1 and B2." When you type the range in the formula, it appears like this, "=COUNTIF (A1:B2, criteria)."

You must also specify that your criteria is text. To do this in Excel, type "*" so that your formula in the empty cell looks like this:

=COUNTIF (A1:B2, *)

5. Apply formula to your worksheet

To apply the formula to the data in your worksheet, click "enter" on your keyboard after inputting the formula and your range. After you click enter, the function counts all cells in that range with text and displays the value in the cell you select for the formula. For example, if your worksheet displays inventory with codes for each item, you can count specific items in the inventory with a text code.

AB1Paper123323021500043CoffeeStraws4=COUNTIF (A1:B3,*)
After entering the formula, the COUNTIF function returns a value of three because there are three cells with text in this example.

Related: Basic Excel Formulas and How To Use Them

Tips for counting cells with text

Here are additional tips for how to count cells with specific types of text:

Counting certain words

=COUNTIF (range; *text*)

You can use this function to count cells with a specific word as long as it appears as text. This can help you determine the word count of your worksheet or ensures that you don't over-use a word. You may also use this function for analysis methods. To use the count function to determine the number of occurrences of a specific word, first type in the range in which you want to check for the word and then the word itself between asterisks.

Using the "COUNTIF" function to count words in Excel allows you to find various forms of the word, too. For example, if you want to count the word "run" this function could also count words that contain run, like "running" or "runs."

Counting text that is case-sensitive

=SUMPRODUCT (--ISNUMBER (FIND ("text"; range)))

If you want to count the number of occurrences for a specific word that is case-sensitive, you can use a combination of Excel formulas. This can help you count words that appear exactly as you type them. To use this function, you can combine the "SUMPRODUCT," "ISNUMBER" and "FIND" functions. The "SUMPRODUCT" function allows you to expand the range of your search, while the "FIND" function allows you to count each cell. To ensure that these functions operate together, you can use the "ISNUMBER" tool.

Related: How To Find and Remove Duplicates in Excel in 4 Steps

Counting text with multiple criteria

=SUMPRODUCT(--(ISNUMBER(FIND("text";range))+ISNUMBER(FIND("second text";range))

You may want to count cells that contain multiple criteria. This means you can search for two different texts at the same time. To do this, you can use the formula above. Similar to counting text that's case-sensitive, this function uses a combination of "SUMPRODUCT," "ISNUMBER" and "FIND" functions. When using this function, create a separate equations string for each text you want to count and combine them with a plus symbol. Then, place both of these equations within the parenthesis for "SUMPRODUCT."

Please note that none of the companies mentioned in this article are affiliated with Indeed.

Explore more articles