Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021 Beginners
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021 Beginners
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > How to Count Cells with Text in Excel? 3 Different Use Cases

How to Count Cells with Text in Excel? 3 Different Use Cases

(Note: This guide on how to count cells with text in Excel is suitable for all Excel versions including Office 365)

Excel deals with a variety of data of diverse data types. Excel can accept data in the form of numbers, text, characters, and operators. When storing large amounts of data, sorting and retrieving one particular type of data can be quite difficult.

In a spreadsheet consisting of different data types, you might sometimes have to pinpoint the cells with text and count them to perform any function or operation. 

In this article, I will tell you how to count cells with text in Excel along with their use cases.

You’ll Learn:

  • What Is Count in Excel?
  • How to Count Cells with Text in Excel?
    • Count All the Text Values
    • Count Cells with Text Value Without Blank Spaces
    • Count Cells with a Particular Text Value
  • Reminders

Watch our video on how to count cells with text in Excel

download wrap text worksheet
How to Count Cells with Text in Excel – Workbook

Related Reads:

How to Count Unique Values in Excel? 3 Easy Ways to Count Unique and Distinct Values

How to Apply the Accounting Number Format in Excel? (3 Best methods)

How to Use Excel COUNTIFS: The Best Guide

What Is Count in Excel?

Before we learn how to count cells with text in Excel, let us refresh the concept of COUNT in Excel. Let us see what COUNT is and how Excel counts the values. 

Counting is one of the most commonly used functions in Excel which provides a numerical summary of the total values which might be useful in a variety of places. In simpler terms, Excel counts how many times a value appears in a column or row.

In Excel, you can count the number of cells or the values it houses by using functions. Excel has a variety of functions that help in counting. Functions like COUNT and COUNTA are used to count all the cells in a particular range or select cells which house a particular value. On the other hand, COUNTIF and COUNTIFS functions count cells only if they satisfy a particular value. 

In the case where we want to count the number of cells with text in Excel, the function COUNTIF/COUNTIFS is the best choice. Using these functions, you can set criteria to the function so it counts only the cells which house the text values in the cell.

How to Count Cells with Text in Excel?

Texts are also called strings in Excel. When it comes to technological terms, strings are a block of text which might contain any identifying values. They can be individual values like name or address, or they can be a variable that points to another constant.

In Excel, they are usually written with alphanumeric characters which are enclosed by double quotation marks or can be written with an apostrophe. Additionally, they can be a result of logical functions like TRUE or FALSE, or may even be special characters like !, @, #, and $.

Count All the Text Values

To count all the text values in the given Excel sheet, you can use the COUNTIF function along with a wildcard character. This function with a wildcard counts all the text values in a given range.

To count the cells with text in Excel, choose a destination cell and enter the formula =COUNTIF(range,criteria). Here, the range denotes the array of cells within which you want the function to act. The criteria variable denotes the condition to satisfy when counting the values.

Consider the below given example. To find the cells with text values in a given range, enter the formula =COUNTIF(A3:A10,”*”). The function COUNTIF acts on the cell range A3 to A10 and finds the text values. The * represents the wildcard element. The * symbol specifies anything other than numbers to be counted, including blank spaces and special characters. However, this method does not count logical values.

How to count cells with text in Excel
How to count cells with text in Excel

The cells with text within a given range are found to be 7. If you count the values manually, you will notice that the cells with text values are 5. But, the cells A11 and A12 also contain characters like space ( ) and apostrophe (‘) which do not show up in the cell.

Also Read:

How to Hide Formulas in Excel? 2 Different Approaches

How to Select Non Adjacent Cells in Excel? 5 Simple Ways

How to Rotate Text in Excel? 3 Effective Ways

Count Cells with Text Value Without Blank Spaces

You can use the same COUNTIF function and wildcards to count the number of cells with text in them. This method only counts cells that hold any text value and does not count any blank cell in the given range.

To count the cells which have text value in them, enter the formula =COUNTIF(range,criteria) in the destination cell. This is the same as the previous case, but adding wildcard “?” together with “*” only counts the cells which have text values.

Consider the below given example. To find the cells with text values in a given range, enter the formula =COUNTIF(A3:A12,”?*”). Here, the function COUNTIF counts the number of cells in the range of cells A3 to A12. Whereas for the criteria parameter, instead of just passing “*” which counts all the cells with text values, adding the “?” wildcard to the criteria counts the cells which have at least one character. 

How to count cells with text in Excel without blank spaces
How to count cells with text in Excel without blank spaces

As result, the cells which have text values are found to be 6. You can see the text values aligned to the left of the cell whereas the numerical values are aligned to the right of the cell. This function also counts the apostrophe as a text character, but it does not count the blank spaces.

Count Cells with a Particular Text Value

With the help of wildcards and the COUNTIF function, you can also count the cells with any specific text values in Excel. 

Using this method, you’ll learn how to count specific words in Excel. To count the cells with the specific value, you can just enter the text to count within quotations when you pass the criteria parameter. 

Let me show you an example. Suppose you want to count the occurrences of the word “two” in a range of cells. You can just enter the formula in the =COUNTIF(A3:A12,”two”) to count the occurrences of the word “two” in the given range of cells A3 to A12.  

How to count cells with text in Excel with particular value
How to count cells with text in Excel with particular value

Wildcards can be used to count cells with a specific text value. 

Imagine you want to count the number of cells that contain the text starting with any particular letter or character. Say, you want to count the number of cells that starts with the letter “t”, then you can use the “*” wildcard.

Consider the example, in the cell range A3 to A12 you want to count the number of cells which start with the letter “t”. Then, enter the function =COUNTIF(A3:A12,“t*”) in the destination cell.

How to count cells with text in Excel with particular value using * wildcard
How to count cells with text in Excel with particular value using * wildcard

The resultant value is 2. This denotes that when you want to find the cells which start with the letter “t”, you can enter the value “t*” in the criteria parameter. As a result, the COUNTIF function counts the cells which start with the letter “t” irrespective of the number of characters that follow them. 

Another case to use the wildcard is to count any one particular value. For example, if you want to find the occurrences of a three-letter word that have the starting letter “t” and ends with “o”, you can use the “?” wildcard. As the “*” wildcard replaces any number of characters, the “?” wildcard only replaces one character.

How to count cells with text in Excel with particular value using ? wildcard
How to count cells with text in Excel with particular value using ? wildcard

In this case, the count function only counts the cells with three characters which start with “t” and end with “o” and counts them.

Suggested Reads:

How to Use SUMPRODUCT Function in Excel? 5 Easy Examples

How to Use the PROPER Function in Excel? 3 Easy Examples

Excel DATEVALUE – A Step-by-Step Guide

Reminders

  • Wildcards can take the place of characters and are of three types: *, ?, and ~. Use the * wildcard when you want to replace more than one character, use the ? wildcard to replace exactly one character, and ~ to replace and search for the exact character. Wildcards are not case-sensitive. Additionally, wildcards only work on text and not on numbers.
  • Sometimes when a cell appears blank, it does not necessarily mean that the cell is empty. Characters like space ( ), apostrophe (‘), and double quotation marks(””) also make the cell look empty. But technically, they are considered text.
  • When looking for cells with text manually, you can easily distinguish the text from the number in Excel. Numbers are aligned to the right of the cell, whereas text is aligned to the left of the cell.

Frequently Asked Questions

How do I count all the cells in Excel?

To count all the cells within a particular range in Excel, you can use the COUNT function followed by the range of cells that you want to count i.e =COUNT(range).

How to count the number of blank cells in Excel?

You can use the COUNTIF or COUNTIFS function with an empty string (““) as the criteria parameter i.e =COUNTIF(range,””) to count the number of blank/empty cells. Or, you can use the =COUNTBLANK(range) function to count them.

How to count in a PivotTable?

In a PivotTable, you can automatically set a category and add fields in the Value Field Setting to count the values. 

Closing Thoughts

In this article, we saw how to count cells with text in Excel. Additionally, we saw all the ways you can use the COUNTIF function to count the text values without including the blank spaces and counting cells with particular words. 

If you need more high-quality Excel guides, please check out our free Excel resources center. Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

How to Use Microsoft Teams – 10 Steps to Get Started in Teams

10 Steps to Know about Task Management on Monday.com

How to Set Up a Chart of Accounts in QuickBooks Pro 2017 Part 2

How to Remove Tables in Excel? 3 Easy Ways 

What is Daily Scrum or Daily Scrum Meeting? [2022]

Working with the QuickBooks Pro 2013 Chart of Accounts

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)