# 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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### Darren Bartrup

##### Well-known Member
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
5
Views
292
Replies
14
Views
712
Replies
4
Views
606
Replies
1
Views
150
Replies
2
Views
277

1,109,348
Messages
5,528,185
Members
409,807
Latest member
nicky736

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...