Count the number of times a certain character appears as the nth character in a column

[HotLanta]

Guys I felt I was close to getting this one but I gave up.

In column D I have a column of product IDs. I want in cell A1 a formula that counts the number of times that "8" appears as the 7th character in each product id in column D (from D1:D100) would suffice.

The product IDs look like this:

CC918S8L
CC919S8L
CC910S8L
CC911S1R
CC912S1L

If I could get help to accomplish this I would modify it later in another cell to count the number of times "R" appears as the 8th character and so forth.

Thanks in advance!

 

[Marcelo Branco]

Try
=SUMPRODUCT(--(MID(D1:D100,7,1)="8"))

M.

 

[HotLanta]

Thank you for your reply Marcelo.

That is similar to what I was trying and I think I see my problem now. To keep my post short and sweet I stated I was looking for the nth character to be "8" and this your formula works.

=SUMPRODUCT(--(MID(D1:D100,7,1)="8"))

I actually have 8 in cell B1 so the formula I was wanting to use would then be:

=SUMPRODUCT(--(MID(D1:D100,7,1)=B1))

This is not working. I wanted it this way so I could change cell B1 to match other conditions I need to look for at other times.

Any ideas? Thanks!

 

[Marcelo Branco]

Try

=SUMPRODUCT(--(MID(D1:D100,7,1)=B1&""))

M.

 

[HotLanta]

This is perfect Marcelo - Thank you very much for your help.

 

[Marcelo Branco]

You're welcome. Glad to help

M.

 

[Rick Rothstein]

Try

=SUMPRODUCT(--(MID(D1:D100,7,1)=B1&""))

For those who might be interested, this should also work (just use the question marks to space out to the character position desired)...

=COUNTIF(D1:D100,"??????"&B1&"?")