Excel formula to calculate the amount of data or cells that contain text or contain certain text in microsoft excel.
In the previous excel formula tutorial we have discussed about excel formulas for counting the number of specific words in an excel cell or range. So what if what we want to count is the number of cells or a lot of data?
In general, to count the number of cells or calculate the amount of data with certain criteria, the excel functions that we can use are Countif function or Countifs function if the conditions we will apply more than one.
Specifically in this excel formula tutorial that we will discuss is counting cells with the condition that the cell contains text both text in general and specific text only.
The application of the following formula will be very useful, for example, to calculate the number of cells containing the codes of certain goods, both in whole and in part of the codes of these goods.
How to count cells containing text in Excel
To count the number of non-empty cells we can take advantage of CountA function. Not empty here means that the contents of the cell can be numbers, date /time format, text, formulas, error values or empty text (“”) generated by a formula.
If you intend to count cells that contain text only then use the following excel formula:
With the formula above then:
- The logical values TRUE and FALSE are not counted as text.
- Cells with number values do not count as text even if they are entered in text-formatted cells.
- Blank cells that start with a single quote or apostrophe (‘) will be counted as text.
Consider the following example:
The excel formula used in cell E3 above is:
With formulas over numbers, error values, blank cells, date/time as well as logical values are fixed as text.
In addition to using the Countif formula as above we can also count the number of cells that contain text by utilizing Sumproduct function in microsoft excel. The general formula is as follows:
On this formula, ISTEXT functions used to determine whether the value of a cell in the data range in question is text or not.
Here’s an example of its application:
Double Strip or double unary ( — ) in the formula as previously explained is used to change the logical value of TRUE/FALSE to the number 1/0 so that it can be calculated arithmetic sum.
For more details please read on the following page: Double Unary Operator ( — ) In Excel
With the excel formulas above, every cell containing text will be counted, then what if we don’t want to count all cells and only want to count cells that contain text or a specific portion?
How to count cells containing specific text in Excel
To calculate the amount of data that contains certain text only one of the formulas that can be used is as follows:
The above discussion I think is enough explained in pemabahasan excel COUNTIF function. for that I will not explain further.
Then what if the text in question is only part of the text. Meaning the cells that are counted are those that contain that particular text, not just cells that contain text data exactly like the criteria in question.
For this kind of case we can utilize whilcard character in Countif function like this:
For example, as in the following example, the formula used to calculate the amount of data/cells containing the text” BJ ” the excel formula used is:
For texts in the form of references, the way they are written is as follows:
Examples of its use as in the following picture:
If you are more careful it turns out that using this formula, each calculated cell does not distinguish between uppercase and lowercase letters (non case sensitive), so that between text “KS” and “Ks” will be the same. If you want case sensitive what’s the solution?
To count cells that contain specific text and are case sensitive (distinguish uppercase and lowercase) use the following excel formula:
Consider the following example:
C4 is a cell reference that is worth the text” Ks “while A2:A10 is a Range reference that we want to count the number of cells filled with the text”Ks”.
FIND function this formula is used to find the position or location of the text in a case sensitive cell. If found, the Find function will generate the sequence number of the text in the form of numbers.
IsNumber Function itself is used to determine whether each result of the Find function was a number if yes it will produce a logical value of TRUE and vice versa if not a number will produce a logical value of FALSE.
The logical value of TRUE and FALSE is then converted to numeric with double unary operator ( — ) so that the number can be calculated by Sumproduct function.
Pretty easy isn’t it?
Thus our discussion of the excel formula for count the number of cells that a given text contains. If there are still problems please submit in the comments section below.
Please also share this excel tutorial post, maybe one of your friends also needs it.
Thanks for visit my blog!