How to count number of cells and count characters in a cell in Excel?
Count digits in a cell and count cells in Excel
MS Excel is a good tool for organizational and accounting things and it can be used at any level. It doesn’t require a lot of experience, because the tool is making everything seem easy. You can use it for your personal need too, with powerful functions such as advanced vlookup in Excel , find the position of a character in a string , or also count number of occurences of a string in a file.
The best part of using the MS Excel is that it has a lot of features and so you can even count the cells and count characters in a cell. Counting the cells manually is no longer a comfortable thing.
How to count number of cells in Excel
First, we will start with counting the cells. Because the MS Excel has a lot of counting features, there’s no need for you to worry if it’s right or wrong.
#1 – Click left on an empty cell, where you want the result to appear. Usually, this happens on the right cell after a row or in the bottom on completed cells.
#2 – The formula tab is right above the cells. Because finding the functions it can be quite challenging, you can simply write =COUNTA on the formula tab. After the word ‘=COUNTA’, you will open some round parenthesis and write the number of the cells from which you want to get a result (e.g C1,C2,C3 etc.).
How to count number of cells: =COUNTA(C1,C2,C3)
If you want to count the cells with a specific criteria (e.g. consecutive numbers), you will use the COUNTIF function. It works the same way as the COUNT function, but you have to change what’s between the round parenthesis.
E.g. You are looking for numbers that are identically with the number in the C19 cell. In the formula tab, you will write: (C1:C2, C19).
How to to count number of cells in Excel matching criteria: =COUNTIF(C1:C99,X)
Also, there is a COUNTIFS function that works with multiple criteria.
How to count number of celles in Excel matching multiple criteria: =COUNTIFS C1:C2, C19, C24:C32, B21)
How to count characters in a cell in Excel
In order to count characters in a single cell in Excel, or to count digits in a cell as well, all you have to do is to use one single function.
Count digits in a cell: =LEN(C1)
How to count characters in multiple cells in Excel
Counting characters in several cells from a text it is again very interesting and not that hard as any other people would say. You only need to know and use some functions. At least the counting is made by a machine.
There are two functions that works for a smart character counting – SUMPRODUCT and LEN.
#1 – You choose the number of cells that have the text you want to be counted.
C1, C2, C3, C4 and C5.#2 – In the formula tab you will first write the SUMPRODUCT function and after the LEN function. Always in this order.
Count digits in cells: =SUMPRODUCT(LEN(B1:B5))
Keep in mind that there’s no need to press the space bar between the parenthesis and the words you are writing when it comes about functions.
Also, the two dots represents the phrase: from …. to …. . In our case: from B1 to B5.
Of course, there are many other functions but this is the one that works well and is very easy to use.
How to count character occurence in cells
If you want to count a specific character in your text, the functions you will need to use are:
SUMPRODUCT, SUBSTITUTE, LENLet’s say you want to see how many times the letter a in lower case appears in your text. And your text is placed in the cells B2 to B9.
The way you will use the functions will be:
Excel count character occurrences in range: =SUMPRODUCT(LEN(B2:B9)-LEN(SUBSTITUTE(B2:B9,a,)))
Using MS Excel functions to count digits in a cell or count characters
Keep in mind that whenever you use a MS Excel function, the round parenthesis are a must. Without them, the function software will not do its job.
Go further with string functions by using powerful functions such as advanced vlookup in Excel , find the position of a character in a string , or also count number of occurences of a string in a file.
How to count colored cells in Excel using formula?
In order to count colored cells in Excel using formula, you must have a column containing numbers.
Add below function on this data range, change the cells from C2:C9 to your own cells, and use the filter on color function.
The result will be the amount of colored cells in Excel counted using a formula.
How to count colored cells in Excel using formula: =SUBTOTAL(102,C2:C9)
