You are currently viewing How to count cells that contain only N character

How to count cells that contain only N character

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 number of columns or number of 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 and 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 for now, we are about to talk about counting cells that contain N characters. N can be any value( the characters you want to count within character.)

To count the number of cells that contain a certain number of characters (N), we normally use the COUNTIF function, and the function 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. Write the formula in front of the columns or on the upper part fx.

2. Select the column you want, or you can write the values within the formula. (when you select the formula, it will fill the rest of the formula automatically.

3. Place a comma and add use quote marks to close the star and close the brackets.

4. If you want to exclude the blank cell, add a question mark with the quote.

5. Press f4 to lock the formula and then press enter to have the results.

To check for the logical statement or values :

6. extent your formula with     +SAMPRODUCT(–ISLOGICAL(–A11, B6)), select the formula, and hit enter.

The result should be 0, where we don't have the logical value of 1 where we have logical values. Logical values are true and false, so our formula will not be able to count them. Excel is simplified, and it's easy to understand.