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

#### mecheet

##### Board Regular
Hi,

I have a number of tables, the 1st is below. This is data i will need to count against.
 1 30A 2 40C 3 40B 4 40A 5 RS+ 6 RS+ 7 1E 8 1E+ 9 1D 10 1D+ 11 1S 12 1S+ 13 2E 14 2E+

<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

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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?

Replies
0
Views
192
Replies
0
Views
811
Replies
8
Views
272
Replies
4
Views
429
Replies
16
Views
540

### Forum statistics

1,203,733
Messages
6,057,051
Members
444,902
Latest member
ExerciseInFutility

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back