EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Statistical Functions in Excel Count Cells with Text in Excel
Secondary Sidebar
Excel Functions
  • Statistical Functions in Excel
    • COUNTIF Formula in Excel
    • COUNTIF Multiple Conditions
    • COUNTIFS with Multiple Criteria
    • Statistics in Excel
    • Confidence Interval in Excel
    • Interpolate in Excel
    • Linear Programming in Excel
    • COUNTIFS in Excel
    • Excel Variance
    • Normal Distribution Formula in Excel
    • Count Cells with Text in Excel
    • Excel Formula For Rank
    • Linear Regression in Excel
    • COUNTA Function in Excel
    • MAX Formula in Excel
    • Excel NORMSINV
    • Count Names in Excel
    • FORECAST Formula in Excel
    • NPER in Excel
    • Excel Median Formula
    • Lognormal Distribution in Excel
    • Excel Chi Square Test
    • Count Formula in Excel
    • COUNTIF Examples in Excel
    • Excel P-Value
    • COUNTIF Not Blank in Excel
    • Excel Standard Deviation Formula
    • Excel GROWTH Formula
    • Excel Percentile Formula
    • Excel Frequency Formula
    • Excel Average Formula
    • Excel Correlation Matrix
    • Excel Z Score
    • Excel MAX IF Function
    • Z TEST in Excel
    • Excel Trendline
    • Excel F-Test
    • Excel STDEV Function
    • Excel Frequency Distribution
    • DCOUNT Function in Excel
    • Excel MIN Function
    • Excel Forecast Function
    • FREQUENCY Excel Function
    • COUNTIF with Multiple Criteria
    • Standard Deviation in Excel
    • MAX Excel Function
    • Excel QUARTILE Function
    • Excel T.Test Function
    • Excel PERCENTILE Function
    • MODE Excel Function
    • SLOPE Excel Function
    • Excel Median Function
    • Excel TREND Function
    • Excel Count Function
    • Excel LARGE Function
    • SMALL Excel Function
    • COUNTIF Excel Function
    • Excel AVERAGE Function
    • Excel CORREL Function
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training Certification
  • Online Excel for Marketing Course

Count Cells with Text in Excel

By Madhuri ThakurMadhuri Thakur

Count Cells with Text in Excel

Count Cells with Text in Excel (Table of Contents)

  • Introduction to Count Cells with Text in Excel
  • How to 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 character/string in it. Ideally, excel was built and developed to work with numbers only. Thus, you can have some of the formulae/methods to count the numbers. This article will look into some of the methods that will help us count cells with text/string values in them.

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.

Start Your Free Excel Course

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

How to Count Cells with Text in Excel?

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

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,639 ratings)
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

All we want is to check the number of text values this entire 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 that there are 7 text values in 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 also have to 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 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 so that we can find out if the given cell value is a text or not. 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

Here in this formula, the ISTEXT function checks if 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, 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, all we want is to find out how many times 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 COUNTIF function.

COUNT example 3-2

Now, use “Andy” as 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 are having 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 range as A2:A10 under 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 have to use SUMPRODUCT in combination with the EXACT function to get exactly the value you wanted. Start typing the SUMPRODUCT formula in excel and add a double minus sign, the same as we did in example 2.

COUNTIF example 5-2

Use EXACT function as an argument under SUMPRODUCT.

COUNTIF example 5-3

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

COUNTIF example 5-4

Then press Enter Key.

COUNTIF example 5-5

This is from this article. Let’s wrap the 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 actually 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 apostrophe (‘) will be considered as texts and also be 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 along with 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
Popular Course in this category
Excel Training (23 Courses, 9+ Projects)
  23 Online Courses |  9 Hands-on Projects |  110+ Hours |  Verifiable Certificate of Completion
4.9
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
Excel for Marketing Training (8 Courses, 13+ Projects)4.8
1 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Excel Course

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Count Cells with Text Excel Template

EDUCBA

Download Count Cells with Text Excel Template

EDUCBA

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

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more