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.
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”.
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.
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
Hi there! I use the ctrl: to enter dates in a cell. I’m trying to add all cells in this column that have this (this will be the only entry in the column) but it continues to show 0 as the function value. I’ve tried all of these steps.
Dear Kelley Sauer,
Please use this formula below to count sales having date.
=COUNTIF(E5:E14, "<>")It will count all sales with date. Using this formula, you will not get zero anymore. Moreover I have also used Ctrl+: to insert date.
With Regards,
Joyanta Mitra