Mrinmoy Roy
While analyzing, organizing, or processing data in Excel, you may feel the necessity to count cells based on the data such as text that the cell contains. Knowing how to count cells based on various criteria is one of the most fundamental skills that you are required to deal well with data in Excel. Understanding this importance, we have come up with four different methods that you can use to count if a cell contains any text in Excel with ease.
We have used a sample monthly chocolate sales report throughout the whole article to demonstrate all 4 methods to count if a cell contains any text in Excel.
Now, we are going to discuss all 4 methods one by one.
1. Using the COUNTIF Function to Count If a Cell Contains Any Text in Excel
You can use the COUNTIF function to count all the cells that contain any kind of text within them.
🔗 Steps:
❶ Select Cell D17 ▶ to store the count result.
❷ Type
=COUNTIF(C5:C14, "*")in the cell.
❸ Press the ENTER button.
General Structure: =COUNTIF(range,”*”)
▶ In the range section, we input C5:C14, which is the range of the column in which we’ve run the COUNTIF function.
▶ The next section following the range holds the criteria, where we input an asterisk (*), which is a wildcard that matches any number of text characters.
2. Adding up If a Cell Contains Any Text in Excel Using the SUMPRODUCT Function
You can use the SUMPRODUCT function instead of the COUNTIF function to count the cells that contain any text within them.
🔗 Steps:
❶ Select Cell D17 ▶ to store the count result.
❷ Type
=SUMPRODUCT(--ISTEXT(C5:C14))❸ Press the ENTER button.
␥ Formula Breakdown
General Structure: =SUMPRODUCT(–ISTEXT(range))
▶ In the range section, we input C5:C14, which is the range of the column in which we’ve run the SUMPRODUCT function.
3. Using the COUNTIFS Function to Count If a Cell Contains Texts with Specific Criteria
If you want to add more criteria while counting cells that contain text then you can use the COUNTIFS function instead of using the COUNTIF function.
In this section, we are going to count only those cells that are not empty; which means we will omit all the cells that hold nothing but empty spaces within.
🔗 Steps:
❶ Select Cell D17 ▶ to store the count result.
❷ Type
=COUNTIFS(C5:C14,"*",C5:C14,"<> ")❸ Press the ENTER button.
␥ Formula Breakdown
General Structure: =COUNTIFS(range,”*”,range,”<> “)
▶ In the range sections, we input C5:C14, which is the range of the column in which we’ve run the COUNTIFS function.
▶ The next section followed by the first range is where we input an asterisk (*). Which is a wildcard that matches any number of text characters.
▶ The last section followed by the second range specifies the second criterion, which leaves all the cells that contain only empty spaces from the total count.
Related Content: How to Count Blank Cells with Condition in Excel
4. Adding up If a Cell Contains Partially Matched Text in Excel
In this section, we are going to count only those cells that contain “Almont” as part of the whole text.
🔗 Steps:
❶ Select Cell D17 ▶ to store the count result.
❷ Type
=COUNTIF(C5:C14,"*"&C17&"*")❸ Press the ENTER button.
␥ Formula Breakdown
General Structure: =COUNTIF(range,”*”&data&”*”)
▶ In the range section, we input C5:C14, which is the range of the column in which we ran the COUNTIF function.
▶ The next section followed the range holds the criteria, where we input the cell address C17 in the place of “data” in “*”&data&”*”. Which extracts only those cells containing the word “Almond”.
Read More: How to Count Non-Blank Cells with Condition in Excel
Things to Remember
📌 The COUNTIF function counts Normal Space as a text value.
📌 Be careful while inserting the range and criteria for each of the functions.
Download the Practice Workbook
You are recommended to download the Excel workbook and practice along with it.
Conclusion
In this article, we have discussed four methods that you can use to count if a cell contains any text in Excel. We have also attached an Excel file that you can download to practice all the methods included in this article. Leave comments in the comment section below regarding problems that you are confronting in Excel, and we will respond to you ASAP.
Related Articles
- How to Count Only Visible Cells in Excel
- How to Count Cells That are Not Blank in Excel
- How to Count Filled Cells in Excel
- How to Count Empty Cells in Excel
- How to Count Filled Cells in Excel Using VBA
- Count Number of Cells with Dates in Excel
- How to Count Odd and Even Numbers in Excel
- How to Count If Cell Contains Number
- How to Count Cells with Specific Value in Excel
- Excel Count Number of Cells in Range
- How to Count Filtered Rows with Criteria in Excel
- VBA to Count Blank Cells in Range in Excel
- How to Count Colored Cells in Excel
The SUMPRODUCT(–ISTEXT(range)) worked for me. My range included TEXT and zeros. Thank you for being there!
Dear Carl,
You are most welcome.
Regards
ExcelDemy