Count Cells with Text in Excel is a crucial function for anyone working with data. It provides the means to quickly and accurately assess the presence of text within a dataset, aiding in data analysis, quality control, and report generation. With Count Cells with Text in Excel, you’re not just counting; you’re gaining insights, making informed decisions, and ensuring data accuracy. Embrace the simplicity and power of this function to elevate your data handling and reporting capabilities in Excel. Whether you’re dealing with lists, surveys, or reports, Count Cells with Text in Excel is an essential tool that ensures you have a clear picture of your data at all times.
This Content Covers:
- How to Count Cells with Text in Excel?
- How to Count Cells with Any Text in Excel?
- How to Count Cells with Specific Text in Excel?
- How to Count Cells with Text Excluding Spaces and Empty strings?
- How to Count Cells with Colored Text in Excel?
1. How to Count Cells with Text in Excel with COUNTIF Function?
Step 1: To count the number of cells, contain text just use the formula given below. We use the COUNTIF function here to count the cells.
=COUNTIF(range,”*”)
Step 2: This Excel formula will give you the calculation of how many cells in the selected range have text in them. The formula counts all cells that contain any text since the asterisk (*) is a wildcard that matches any string of characters.
2. How to Count Number of Cells with Text in Excel by Formula?
Suppose you want to count cells from a table or worksheet in a spreadsheet that has different sorts of text and some of them are not visible to us such as cells with special characters, numbers formatted as text, visually blank cells which has empty texts, space, etc. To count these sorts of cells also you can follow the instructions below.
Step 1: Select cell C3 and insert the formula given below.
=SUMPRODUCT(ISTEXT(range)*1)
Step 2: Click Enter. Now you can see that the formula has counted the empty cells and the cell will number as well.
3. Way to Count Cells with Specific Text in Microsoft Excel?
Count cells with certain text
Suppose you want to count cells that have a specific text in them. You can do this just by replacing the asterisk symbol with that text in the COUNTIF formula.
Step 1: Insert the formula from the picture below in any cell containing the specific text you are looking for.
=COUNTIF(range,”Text”)
Step 2: Hit the Enter key. As you can see there are three Farhans in the selected range so the formula also returned number 3 in the cell that we applied the formula into. If there were only one Farhan then the formula would return 1. You can also use this method to check if a particular text is present more than once in a datasheet or not.
Step 3: Now suppose instead of a full name you just want to put a single letter as text to see how many of these names have that specific text. This formula in the below picture has returned 4 because Rishad, Synthia, Laboni, and David all of these 4 names have an “i” inside their name.
4. How to Count Cells with Text Excluding Spaces and Empty strings?
Occasionally, cells that appear to be empty but really contain characters that are invisible to human sight, such as empty strings, apostrophes, spaces, line breaks, etc., may only be empty on the surface. As a result, the formula counts a visually blank cell. Such as the picture below, there are four cells that has text in it one empty looking cell has a space character in it and the other empty-looking cell has empty text string so the formula returned 6.
To count cells correctly in these sorts of situations, with text excluding space and empty string, follow the steps below.
Step 1: Select a cell and insert the formula given below. Click on Enter key and now you can see the formula has excluded spaces and empty strings while counting cells with strings. We use COUNTIFS function to count cells in the range.
=COUNTIFS(range,”*”, range, “<> “)
5. How to Count Cells with Colored Text in Excel Sheet? Advanced Excel Formula.
Step 1: Insert the following formula in a cell where you want the count to be displayed and hit Enter key. This will count how many cells in that range have data in them. The number 2 in the formula is the numerical indicator for the COUNT function. Enter the formula as below:
=SUBTOTAL(2,range)
Step 2: Right click on a cell containing a colored text that you want to use as filter to get the count of that colored text and select Filter>>Filter by Selected Cell’s Font Color.
Step 3: As soon as you do the previous step, all the other colored text cells will be hidden and the cell in which you inserted the SUBTOTAL formula will show you how many of those particular-colored cells are there in that table.
Step 4: Now if you wish to extract other colored text cell’s count, just click on the filter icon and choose any other color from Filter by Color option.
For ready-to-use Dashboard Templates:
