Countif & Mid

[kevbez1969]

Hi,

I have a range of cells (AC54 to AC91)
Each cell contains a string of numbers and letters (e.g. 112111111211111121111cnyy)

I need a formula to count how many times 'y' appears in positions 23, 24 & 25 in the strings on each cell in the range.

Thanks in advance for any help you can give.

 

[Gerald Higgins]

Are you looking for an answer of 2 in that example ?
Try this

=LEN(MID(AC54,23,3))-LEN(SUBSTITUTE(MID(AC54,23,3),"y",""))

 

[jasonb75]

Try

=SUM(--(MID(AC54,{23,24,25},1)="y"))

 

[kevbez1969]

Thanks Jason, that works for the one cell.

How do I then get a total of y's on the whole range of cells, AC54 to AC91?

 

[jasonb75]

Sorry, I thought you wanted per cell, this should fix it

=SUMPRODUCT(--(MID(AC54:AC91,{23,24,25},1)="y"))