How to count number of cells and count characters in a cell 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.


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 number cells in excel?

There are three easy ways to number rows in Excel in order.

1. The first and easiest way to automatically number rows in an Excel spreadsheet.

You will have to manually enter the first two numbers - and these do not have to be 1 and 2. You can start counting from any number. Select the cells with the LMB and move the cursor over the corner of the selected area. The arrow should change to a black cross.

When the cursor turns into a cross, press the left mouse button again and drag the selection to the area in which you want to number the rows or columns. The selected range will be filled with numeric values ​​in increments, as between the first two numbers.

2. A way to number lines using a formula.

You can also assign a number to each line through a special formula. The first cell must contain the seed. Specify it and move on to the next cell. Now we need a function that will add one (or another necessary numbering step) to each subsequent value. It looks like this:

"=[cell with first value] + 1"

In our case, this is =A1+1. To create a formula, you can use the SUM button from the top menu - it will appear when you put the = sign in the cell.

We click on the cross in the corner of the cell with the formula and select the range to fill in the data. The lines are numbered automatically. 3. Row numbering in Excel using progression.

You can also add a marker in the form of a number using the Progression function - this method is suitable for working with large lists if the table is long and contains a lot of data.

  • In the first cell - we have it A1 - you need to insert the initial number.
  • Next, select the required range, capturing the first cell.
  • In the Home tab, you need to find the Fill function.
  • Then select Progression. The default parameters are suitable for numbering: type - arithmetic, step = 1.
  • Click OK and the selected range will turn into a numbered list.

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.

Become an Excel Pro: Join Our Course!

Elevate your skills from novice to hero with our Excel 365 Basics course, designed to make you proficient in just a few sessions.

Enroll Here

Elevate your skills from novice to hero with our Excel 365 Basics course, designed to make you proficient in just a few sessions.

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, LEN

Let'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)

Frequently Asked Questions

What formulas or functions can be used in Excel to determine the total number of cells in a range that contain data and to count the characters within a specific cell?
To count the number of non-empty cells in a range, use the `COUNTA(range)` function. To count the number of characters in a cell, including spaces, use the `LEN(cell_reference)` function. These functions provide simple ways to analyze cell content and data length within Excel spreadsheets.

Complete 2019 Excel for Beginners in video


Become an Excel Pro: Join Our Course!

Elevate your skills from novice to hero with our Excel 365 Basics course, designed to make you proficient in just a few sessions.

Enroll Here

Elevate your skills from novice to hero with our Excel 365 Basics course, designed to make you proficient in just a few sessions.



Comments (0)

Leave a comment