Excel is a powerful and very helpful Microsoft program for data analysis and documentation. OK is a spreadsheet program with many columns and rows, and sometimes we need to know the number of cells and the number of columns or characters within the cell. As we all know, every cell contains one point of data or a piece of information; this enables us to find information from changing data.
We have a lot of calculations in excel, for example.
1. Count characters within cells with text →LEN(A1).
2. Count certain characters in a cell → LEN(A1)-LEN(SUBSTITUTE(A1," a,"))
3. Count characters in several cell→ sum(LEN(range))
and many more, but we are about to talk about counting cells that contain N characters for now. N can be any value( the characters you want to count within character.)
To count the number of cells containing a certain number of characters (N), we normally use the COUNTIF function, which is used when the number of cells meets certain properties.
How the formula works
The syntax looks like COUNTIF(criteria_range, criteria(A, N)).
Range= number of cells to count.
N=characters to count.
To count the number of cells that have a certain number of characters (let's say seven characters we can use A1:B6), excel users can come up with a formula COUNTIF(A1:B6,"?????")
OR COUNTIF (A1:B6, REPT("?",7)). The output Will return a text string made of exactly seven question marks. The above formula will (COUNTIF(A1:B6,"?????")by using the question marks character to match one character. So if you need more characters to be matched, you will have to the number of characters you need in the formula. The REPT function will give back a text string made of exactly the number you want( The number of n characters you added to the formula)
The COUNTIF function will be able to count all cells in the range that contain exactly seven characters.
Steps on how to count cells with N character
The formula will not count cells with numbers and logical statements or values that are TRUE and FALSE.
1. Let's use the following data as an example to count the number of cells with five characters.
2. Write the formula =COUNTIF(A1:D7,"?????") in an empty cell. The 5 question marks represent the number of characters. You can change the range and number of question marks to match your preference.
3. Press Enter, and you will get the number of cells with five characters.
1. Let's count the number of cells with four characters in our piece of data
2. We will use the IF function again =COUNTIF(A1:D7,"????")
3. Press Enter and the number of cells with four characters will be displayed
How to count Cells that contain digits only
The COUNT function counts the number of cells that contain digits.
The Syntax is COUNT(RANGE)
For example, we can count the cells that contain numbers in the data above following these steps.
1. Enter the formula in a blank cell
2. Press enter, and the number of cells that contain digits will be populated.