Count spesific number in a string of number in excel

Question

I have an ID with the format is : xxxxxxxx ( 8 numbers). Example : 16515134

3 first numbers is faculty code (In the example : 165) 2 numbers after that is year you enter the university (In the example : 15) 3 last numbers is personal number (In the example : 134)

I have about 100 hundred IDs in 100 rows in one cell.

I want to count the specific 4th and 5th number position (year you enter the university).

Example :

16515134

16515145

13216124

I want to count the "15" (the 4th dan 5th number). So it will give 2 as the answer.

So how to do that ? I'm already tried use =COUNTIFS(A1:A6,"***15*") but it only works when it is a text not a number.

While posting your question in the "Title" field you were asked "What is your programming question?". So what is your *programming* question? — Al.G., Oct 14 '16 at 17:32

score 3 · Accepted Answer · answered Oct 14 '16 at 17:19

3

Something like:

=SUMPRODUCT(--(MID(A1:A20,4,2)="15"))

answered Oct 14 '16 at 17:19

Gary's Student

95,722
10
59
99

beat you to it, this time. :) – Scott Craner Oct 14 '16 at 17:21
but i am want know what does the "--" (without "") mean? – Shalahuddin Al-Ayyubbi Oct 14 '16 at 23:57
@ShalahuddinAl-Ayyubbi the material inside the **()** is an array of **TRUE** / **FALSE** ..............the double **-** changes that to an array of **0/1** – Gary's Student Oct 15 '16 at 00:51

Count spesific number in a string of number in excel

1 Answers1