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

mecheet

Board Regular
Joined
Apr 21, 2010
Messages
117
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,651
Messages
5,838,571
Members
430,556
Latest member
Peachforyou

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?

Disable AdBlock

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
Top