EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Statistical Functions in Excel Count Cells with Text in Excel
 

Count Cells with Text in Excel

Madhuri Thakur
Article byMadhuri Thakur

Updated June 9, 2023

Count Cells with Text in Excel

 

 

Introduction to Count Cells with Text in Excel

In our day to day life, you must have faced some situations where you want to count out the cells which are having text values or characters/strings in them. Ideally, excel was built and developed to work with numbers only. Thus, you can have some formulae/methods to count the numbers. This article will look into some methods to help us count cells with text/string values.

You will be going through five methods for counting cells with text values. All of them are listed below:

  1. COUNTIF function + Wildcard character to count cells with text in Excel.
  2. SUMPRODUCT + ISTEXT functions to count cells with text in Excel.
  3. COUNTIF function to count cells with specific string/text in Excel.
  4. COUNTIF function to count cells with partial text values in Excel.
  5. SUMPRODUCT and EXACT function to count case sensitive text values (Values such as all CAPS).

We will go through each method one by one and step by step.

How to Count Cells with Text in Excel?

Counting cells with Text in Excel is very simple and easy. Let’s understand how to use the Count Cells with Text in Excel with some examples.

You can download this Count Cells with Text Excel Template here – Count Cells with Text Excel Template

Example #1 – Count Cells in Excel using COUNTIF Function and the Wildcard Character

Suppose we have a list of employees and some salary information as below:

count cells with text in excel 1-1

We only want to check the number of text values this list contains. We can use a combination of COUNTIF and a wildcard letter (asterisk or *) to find the number of text values this list contains.

In cell B2, start typing the formula for the Excel COUNTIF function.

count cells with text in excel 1-2

As the requirement of the COUNTIF function, set range as an array from A2:A12 as a first argument.

count cells with text in excel 1-3

Use an asterisk (*) as a second argument which is the criteria you give to the COUNTIF function. Don’t forget to enclose it in double quotes.

count cells with text in excel 1-4

Then press Enter Key.

count cells with text in excel 1-5

You can see in cell C2; the number 7 denotes 7 text values in the range A2:A12. The key argument here is a wildcard asterisk. It allows the COUNTIF function to count any number of characters except numbers or logical values. You must also note that those will be counted in this formula if numbers are stored as texts.

Example #2 – Count Cells with Text using SUMPRODUCT and ISTEXT Function

SUMPRODUCT, with the help of ISTEXT, also allows you to count the cells having text values in them. In cell B2, start typing the SUMPRODUCT formula.

Count Cells with Text in Excel -2

Now use the minus sign two times under the SUMPRODUCT formula. Don’t worry; I will break this logic for you.

Count Cells with Text in Excel 2-2

Start typing the ISTEXT formula under this SUMPRODUCT formula to determine whether the given cell value is a text. If the given cell value is a text, it will be mentioned as TRUE. Else, FALSE. That means ISTEXT is a function that returns an array of logical values for a given range of cells.

Count Cells with Text in Excel 2-3

Use array A2: A12 as an input argument to the ISTEXT function.

Count Cells with Text in Excel 2-4

Then press Enter Key.

Count Cells with Text in Excel 2-5

In this formula, the ISTEXT function checks whether each value from array A2:A12 is text or not. If text – TRUE. If not – FALSE. Therefore, we get an array of TRUE and FALSE for each cell. The double minus sign helps the system to convert TRUE and FALSE values into 1’s and 0’s (numeric conversions of TRUE and FALSE, respectively) so that the SUMPRODUCT function can sum them up and give a final count.

Example #3 – COUNTIF Function to Count Specific Text/String

Suppose we have data as the screenshot shown below: 

Count Cells with Text in Excel 3

Now, we only want to find out how often the name “Andy” is occurring on this list. Start typing the COUNTIF formula in cell B2.

Count Cells with Text in Excel 3-1

As the first argument to this formula, put A2:A13 as a range in the COUNTIF function.

COUNT example 3-2

Now, use “Andy” as the criteria under the COUNTIF function. It allows a function to count the number of times “Andy” has occurred in the range of cells.

count example 3-3

Then press Enter Key.

count example 3-4

This is how you can find the count of specific text occurring in the given range of cells.

Example #4 – COUNTIF Function to Count Partial Text

Suppose we have data as shown in the below screenshot.

count example 4-1

All we want is the count of Employee ID starting with “DAR”. Start typing the COUNTIF formula in cell B2.

count example 4-2

Set the range as A2:A10 under the COUNTIF function.

count example 4-3

Under criteria, use “DAR*”. It allows COUNTIF to count all the cells having DAR and anything in front of it.

COUNT Example 4-4

Then press Enter Key.

count example 4-5

Example #5 – Count Case Sensitive Text using SUMPRODUCT and EXACT Function

Suppose you have data as shown in the below screenshot.

COUNTIF example 5-1

You want to find the count of “ANDY” (word Andy with all caps). This is a different scenario. Therefore, you must use SUMPRODUCT with the EXACT function to get your desired value. Start typing the SUMPRODUCT formula in Excel and add a double minus sign, as in example 2.

COUNTIF example 5-2

Use the EXACT function as an argument under SUMPRODUCT.

COUNTIF example 5-3

Add “ANDY” as the criteria and set the range as A2:A12 under the EXACT function.

COUNTIF example 5-4

Then press Enter Key.

COUNTIF example 5-5

This is from this article. Let’s wrap things up with some points to remember.

Things to Remember About Count Cells with Text in Excel

  • Logical Values TRUE & FALSE are not counted as text values. Because in computer language, logical TRUE = 1 and logical FALSE = 0, which are numeric values.
  • Blank cells will be treated as text values.
  • Numbers are not counted using an asterisk (“*”) unless those are stored as text values.
  • Blank cells starting with an apostrophe (‘) will be considered texts and counted.

Recommended Articles

This is a guide to Count Cells with Text in Excel. Here we have discussed How to Count Cells with Text in Excel, a few practical examples, and a downloadable Excel Template. You can also go through our other suggested articles –

  1. Count Colored Cells In Excel
  2. Excel Text with Formula
  3. Count Names in Excel
  4. VBA Count

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Count Cells with Text Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Count Cells with Text Excel Template

EDUCBA

डाउनलोड Count Cells with Text Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW