Count / MAX with alphanumerical references

[alphaexcel]

I am trying to produce a count/max based on an ascending numerical reference that has a letter (identifer) placed before it. e.g.

R1
R2
R3

C1
C2
C3
C4

I have tried several count functions/sumproduct but cannot get any to work, even using the MID function. Any ideas?

 

[Richard Schollar]

Hi

If you want the max beginning R then could you use:

=COUNTIF(A:A,"R*")

replace R with C etc.

 

[alphaexcel]

Hmm strange. This was the first lookup i tried and it did not work. But laying it out as you ahve shown it produces the desired result

Many thanks

 

[njimack]

If you're looking for the highest value, regardless of the alpha-identifier, try:
=MAX(RIGHT(A:A))