When you are working with data in Excel, sometimes you need to count cells that have text on it. In case you usually do his by hand since you’re not dealing with a lot of data, the reality is that Excel makes your life a lot easier when this happens.
Check out our simple rounding numbers calculator.
The CountIf Function
In case you don’t know or are not very familiar with this Microsoft program, then you will be glad to know that you can count cells with text easily by simply using the CountIf function.
=COUNTIF(rng,”*txt*”)
Above, you can see the generic formula that you need to use when you need Excel count cells with text. Notice that:
- rng: is a range of cells
- txt: represents the text that the cells should contain
- *: is a wildcard that matches any number of characters.
Let’s use the formula:
=COUNTIF(B4:B11,”*a*”)
Learn how to round numbers up in Excel.
How The Excel Count Cells With Text Formula Works
Simply put, the formula will count the number of cells in the range that contain “a” by simply matching the content of each cell against the patter “*a*”. In case you don’t remember, when you use the “*” in Excel you are simply stating that the program should match any number of characters. So, by looking at the formula displayed above, this means that this pattern will look for any cell that contains “a” in any position. The count of the cells that match this criteria will then be returned as a number.
One of the best things about this formula is that you can adjust it very easily to use the contents of another cell for the criteria. Let’s say that the cell C7 contains the text that you want to match. In this specific case, you would then need to use the following formula:
=COUNTIF(rng,”*”&c7&”*”)
Case-Sensitive Text
On some occasions, you may simply want to look for cells with case-sensitive text. In these specific cases, you won’t be able to use the CountIf function; instead, you will need to use the FIND function which is case sensitive.
This is how you round numbers in Python.
How To Count Excel Cells With Specific Text
Let’s now say that you just want to count Excel cells with specific text. Imagine that you have a list of items IDs in the cells between A2 and A10 (A2:A10). Here, you just want to find the cells that have a specific ID: “AA-01”.
So, all you need to do is to use the CountIf function again but it will look a bit different:
=COUNTIF(A2:A10, “AA-01”)
Then, to ensure that you can actually count the cells with this specific text and you don’t want to change the formula, you can simply input the text in a predefined cell. Let’s say that you pick the cell D1:
=COUNTIF(A2:A10, D1)
As you can see, counting Excel cells with text when you are dealing with huge data doesn’t need to be hard or take you a lot of time. You just need to use the right formula.