Help Needed - Count for a range when numbers are represented by letters

[mecheet]

Hi,

I have a number of tables, the 1st is below. This is data i will need to count against.

1</SPAN>	30A</SPAN>
2</SPAN>	40C</SPAN>
3</SPAN>	40B</SPAN>
4</SPAN>	40A</SPAN>
5</SPAN>	RS+</SPAN>
6</SPAN>	RS+</SPAN>
7</SPAN>	1E</SPAN>
8</SPAN>	1E+</SPAN>
9</SPAN>	1D</SPAN>
10</SPAN>	1D+</SPAN>
11</SPAN>	1S</SPAN>
12</SPAN>	1S+</SPAN>
13</SPAN>	2E</SPAN>
14</SPAN>	2E+</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

I then have another sheet which contains data where there are a number of rows where the 2nd column values are used.

I want to work out how many are below 1e, so between value 1-6, how many are for value 7 and how many are 8 and above.

Thanks
​

 

[Darren Bartrup]

Not fully sure what you're after, but here goes.....

I've put the second column of your table in range B1:B14.

Use D1 to show which value you want to look at - so in your example 1E would go in cell D1.
The formula =MATCH($D$1,$B$1:$B$14,0) will give you the position of 1E within the list - in your case it will return 7 as 1E is the 7th item in the range.
The formula =COUNTA($B$1:INDEX($B$1:$B$14,$E$1-1)) will count how many items are above position 7 - (the cell reference $E$1 references the MATCH formula).
The formula =COUNTA(INDEX($B$1:$B$14,$E$1+1):INDEX($B$1:$B$14,COUNTA($B$1:$B$14))) will count how many items are below position 7.

Is this what you're after?