Bạn đang xem bài viết Excel Formula: Count Total Words In A Cell được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Excel doesn’t have a dedicated function for counting words in a cell. However, with a little ingenuity, you can create such a formula using the SUBSTITUTE and LEN functions, with help from TRIM, as shown in the example. At a high level, this formula uses the LEN function to count the number of characters in the cell, with and without spaces, then uses the difference to figure out the word count. This works, because word count is equal to the number of spaces + 1, so long as there is one space between each word.
The first part of the formula counts the characters in cell B5, after removing extra space:
// normalize space, count characters
Inside LEN, the TRIM function first removes any extra spaces between words, or at the beginning or end of the text. This is important, since any extra spaces will throw off the word count. In this case, there are no extra space characters, so TRIM returns the original text directly to the LEN function, which returns 30:
"All Quiet on the Western Front"
// returns 30
At this point, we have:
Next, we use the SUBSTITUTE function to remove all space characters from the text:
// strip all space
Notice SUBSTITUTE is configured to look for a space character (” “), and replace with an empty string (“”). By default, SUBSTITUTE will replace all spaces. The result is delivered directly to the LEN function, which returns the count:
// returns 25
LEN returns 25, the number of characters remaining after all space has been removed. We can now simplify the formula to:
// returns 6
which returns 6 as a final result, the number of words in cell B5.
Dealing with blank cells
The formula in the example will return 1 even if a cell is empty, or contains only space. This happens because we are adding 1 unconditionally, after counting space characters between words. To guard against this problem, you can adapt the formula as shown below:
Notice we've replaced 1 with this expression:
This code first trims B5, then checks the length. If B5 contains text, LEN returns a positive number, and the expression returns TRUE. If B5 is empty, or contains only space, TRIM returns an empty string ("") to LEN. In that case, LEN returns zero (0) and the expression returns FALSE. The trick is that TRUE and FALSE evaluate to 1 and zero, respectively, when involved in any math operation. As a result, the expression only adds 1 when there is text in B5. Otherwise, it adds zero (0). This logic could also be written with the IF function statement like this:
and the result would be the same. The expression above is simply more compact.
This short tutorial explains the basics of the Excel COUNT and COUNTA functions and shows a few examples of using a count formula in Excel. You will also learn how to use the COUNTIF and COUNTIFS functions to count cells that meet one or more criteria.
As everyone knows, Excel is all about storing and crunching numbers. However, apart from calculating values, you may also need to count cells with values – with any value, or with specific value types. For example, you may want a quick count of all items in a list, or the total of inventory numbers in a selected range.
Microsoft Excel provides a couple of special functions for counting cells: COUNT and COUNTA. Both all very straightforward and easy-to-use. So let’s take a quick look at these essential functions first, and then I will show you a few Excel formulas to count cells that meet certain condition(s), and clue you in on the quirks in counting some value types.
Excel COUNT function – count cells with numbers
You use the COUNT function in Excel to count the number of cells that contain numerical values.
The syntax of the Excel COUNT function is as follows:
COUNT(value1, [value2], …)
Where value1, value2, etc. are cell references or ranges within which you want to count cells with numbers.
In the modern versions of Excel 2016, Excel 2013, Excel 2010, and Excel 2007, the COUNT function accepts up to 255 arguments. In earlier Excel versions, you can supply up to 30 ‘values’.
For example, the following formula returns the total number of numeric cells in range A1:A100:
Note. In the internal Excel system, dates are stored as serial numbers and therefore the Excel COUNT function counts dates and times as well.
Using COUNT function in Excel – things to remember
Below are the two simple rules by which the Excel COUNT function works.
If an argument(s) of an Excel Count formula is a cell reference or range, only numbers, dates and times are counted. Blanks cells and cells containing anything but a numeric value are ignored.
If you type values directly into the Excel COUNT arguments, the following values are counted: numbers, dates, times, Boolean values of TRUE and FALSE, and text representation of numbers (i.e. a number enclosed in quotation marks like “5”).
For example, the following COUNT formula returns 4, because the following values are counted: 1, “2”, 1/1/2016, and TRUE.
=COUNT(1, "apples", "2", 1/1/2016, TRUE)
Excel COUNT formula examples
And here are a few more examples of using the COUNT function in Excel on different values.
To count cells with numeric values in one range, use a simple count formula like
The following screenshot demonstrates which types of data are counted and which are ignored:
To count several non-contiguous ranges, supply all of them to your Excel COUNT formula. For example, to count cells with numbers in columns B and D, you can use formula similar to this:
If you want to count numbers that meet certain criteria, use either the COUNTIF or COUNTIFS function.
Excel COUNTA function – count cells with values (non-blank cells)
The COUNTA function in Excel counts the number of cells in a range that are not empty.
The syntax of the Excel COUNTA function is akin to that of COUNT:
COUNTA(value1, [value2], …)
Where value1, value2, etc. are cell references or ranges where you want to count non-blank cells.
For example, to count cells with value in range A1:A100, use the following formula:
To count non-empty cells in several non-adjacent ranges, use a COUNTA formula similar to this:
=COUNTA(B2:B10, D2:D20, E2:F10)
As you can see, the ranges supplied to an Excel COUNTA formula do not necessarily need to be of the same size, i.e. each range may contain a different number of rows and columns.
Please keep in mind that Excel’s COUNTA function counts cells containing any type of data, including:
Dates / times
Boolean values of TRUE and FALSE
Error values like #VALUE or #N/A
Empty text strings (“”)
In some cases, you may be perplexed by the COUNTA function’s result because it differs from what you see with your own eyes. The point is that an Excel COUNTA formula may count cells that visually look empty, but technically they are not. For example, if you accidentally type a space in a cell, that cell will be counted. Or, if a cell contains some formula that returns an empty string, that cell will be counted as well.
In other words, the only cells that the COUNTA function does not count are absolutely empty cells.
The following screenshot demonstrates the difference between Excel COUNT and COUNTA functions:
Tip. If you just want a quick count of non-blank cells in a selected range, simply have a look at Status Bar at the bottom right corner of your Excel window:
If you just want a quick count of, simply have a look at Status Bar at the bottom right corner of your Excel window:
Excel COUNTIF function – count cells that meet one condition
The COUNTIF function is purposed for counting cells that meet a certain criterion. Its syntax requires 2 arguments, which are self-explanatory:
In the first argument, you define a range where you want to count cells. And in the second parameter, you specify a condition that should be met.
For example, to count how many cells in range A2:A15 are “Apples”, you use the following COUNTIF formula:
Instead if typing a criterion directly in the formula, you can input a cell reference as demonstrated in the following screenshot:
For more information about using the COUNTIF function in Excel, check out the following tutorial: COUNTIF in Excel – count if not blank, greater than, duplicate or unique
Excel COUNTIFS function – count cells that match several criteria
The COUNTIFS function is similar to COUNTIF, but it allows specifying multiple ranges and multiple criteria. Its syntax is as follows:
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
The COUNTIFS function was introduced in Excel 2007 and is available in all later versions of Excel 2010, 2013, and 2016.
For example, to count how many “Apples” (column A) have made $200 and more sales (column B), you use the following COUNTIFS formula:
And again, to make your COUNTIFS formula more versatile, you can supply cell references as the criteria:
You will find plenty more formula examples here: How to use Excel COUNTIFS function with multiple criteria.
Count the number of cells in a range (ROWS and COLUMNS functions)
If you need to find out the total number of cells in a rectangular range, utilize the ROWS and COLUMNS functions, which return the number of rows and columns in an array, respectively:
For example, to find out how many cells there are in a given range, say A1:D7, use the following formula:
You may also be interested in
There is one option which I always wish Excel should have and that’s count number of words from a cell.
If you work in MS Word there is an inbuilt option on the status bar which shows you how many words are there in the sheet.
But when it comes to Excel there is no such option to count words. You can count the number of cells which have text but not actual words in them.
As you know, in Excel, we have functions and you can use them to calculate almost everything. You can create a formula that can count words from a cell.
Today in this post, you will learn how to count words in Excel from a cell, or a range of cells or even from the entire worksheet. And I’ll also show you how to count a specific word from a range of cells.
Four Different ways to Count Words in Excel
Now without any ado, let’s get started.
1. The Formula to Count Words from a Cell
To count words from a cell you need to combine LEN function with SUBSTITUTE function . And the formula will be (Text is in cell A1):=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1
When you refer to a cell using this formula, it will return 7 in the result. And yes, you have a total of 7 words in the cell.
Before getting into this formula just think this way. In a normal sentence if you have eight words then you will definitely have 7 spaces in those words.
That means you will always have one word more than the spaces. The idea is simple: If you want to count the words, count the spaces and add one in it. Now, to understand this formula you need to split it into three parts.
In the first part, you have used LEN function to count the numbers of characters from the cell A1.
At this point, you have an equation like this.
The total number of characters with spaces and the total number of characters without spaces.
And when you subtract both of these numbers gets the number of spaces and in the end, you have to add one in it. It returns 7 in the result which is the total number of words in the cell.
When you use the above formula it will return 1 even if the cell is blank so it’s better to wrap it with IF function to avoid this problem.
This formula will first check the cell and only return word count if there is a value in the cell.
Apart from the above formulas, I have written a small code to create a UDF for this. This code will help you to create a custom function which will simply return the word count.
In short, you don’t need to combine any functions.
Let me tell you how to use it.
First of all, enter this code in VBA editor.
And then come back to your worksheet, and enter “=MyWordCount(” and refer to the cell in which you have value.
And, it will return the word count.
2. Count Words from a Range of Cells
Now let’s come to the next level.
And here you need to count words from a range of cells instead of a single cell.
The good news is you just need to use the same formula (just a simple change) which you have used above.
In the above formula, A1:A11 is the range of cells and when you enter the formula it returns 77 in the result.
here’s how it works
Do you remember that SUMPRODUCT can take arrays? So when you use it, it returns an array where you have a count of words for each cell.
And in the end, it sums those counts and tells you the count of words in the column.
3. Word Count from Entire Worksheet with VBA Code
This code is one of my useful macro codes list which I use in my work and it can help you to count all the words from a worksheet.
Dim WordCnt As Long
Dim rng As Range
Dim S As String
Dim N As Long
N = 0
N = Len(S) – Len(Replace(S, ” “, “”)) + 1
WordCnt = WordCnt + N
4. Count a Specific Word/Text String from a Range
Here you have a different situation.
Let’s say you need to count a specific word from a range of cells or to check the number of times a value appears in a column.
Below you have a range of four cells and from this range, you need to count the count of occurrence of the word “Monday” . For this, the formula is:
And when you enter it, it returns the count of word “Monday”.
It returns the count of the word (word’s frequency) from the range not the count of the cells which have that word.
Monday is there four times in three cells.
…let me explain how it works
To understand this function, again you need to split it into four parts.
In the first part, LEN function returns an array of the count of characters from the cells.
The second part returns an array of the count of character from the cells by removing the word “Monday”.
In the third part, LEN function returns the length of characters of wor word “Monday”.
After that, subtracts part one from part two and then divide it with part three…
…it returns an array with the count of the word “Monday” from each cell.
In the fourth part, SUMPRODUCT returns the sum of this array and give the count of “Monday” from the range.
Whenever you are typing some text in a cell or a range of cells you can these methods to keep a check on the word count.
I wish someday in future Excel will get this option to count words. But, for the time being, you have all these awesome methods.
Which method do you like the most?
At the core, this formula uses the INDEX function to retrieve 10 random names from a named range called “names” which contains 100 names. For example, to retrieve the fifth name from the list, we use INDEX like this:
However, the trick in this case is that we don’t want a single name at a known location, we want 10 random names at unknown locations between 1 and 100. This is an excellent use case for the RANDARRAY function, which can create a random set of integers in a given range. Working from the inside out, we use RANDARRAY to get 10 random numbers between 1 and 100 like this:
The COUNTA function is used to get a dynamic count of names in the list, but we could replace COUNTA with a hardcoded 100 in this case with the same result:
In either case, RANDARRAY will return 10 numbers in an array that looks like this:
Note: these numbers are random only and do not map directly to the example shown.
This array is returned directly to the INDEX function as the row argument:
Because we are giving INDEX 10 row numbers, it will 10 results, each corresponding to a name at the given position. The 10 random names are returned in a spill range beginning in cell D5.
One problem with the above formula (depending on your needs) is that RANDARRAY will sometimes generate duplicate numbers. In other words, there is no guarantee that RANDARRAY will return 10 unique numbers.
To ensure 10 different names from the list, you can adapt the formula to randomly sort the full list of names, then retrieve the first 10 names from the list. The formula in F5 uses this approach:
The approach here is the same as above – we are using INDEX to retrieve 10 values from the list of names. However, in this version of the formula, we are sorting the list of names randomly before handing giving the list to INDEX like this:
Here, the SORTBY function is used to sort the list of names randomly with an array values created by the RANDARRAY function, as explained in more detail here.
Finally, we need to retrieve 10 values. Because we already have names in a random order, we can simply request the first 10 with an array created by the SEQUENCE function like this:
SEQUENCE builds an array of sequential numbers:
which is returned to the INDEX function as the row argument. INDEX then returns the first 10 names in a spill range like the original formula.
Cập nhật thông tin chi tiết về Excel Formula: Count Total Words In A Cell trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!