Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Counting Numbers with Text
Hi, Is it possible to count how many top 10's there have been in a row of data that has text with the numbers ie: jp5 ,, jp23 ,, as6 ,, as50 ,, as100 ,, 5 is this data was in a row on the spreadsheet i want it to return that there were 3 entries that were 10 or under someone gave me thsu formula to colour using conditional formatting for a similar problem i had if that helps =--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT ("1:100")),1)),0),100-SUM(--ISERROR(--MID(A1,ROW(INDIRECT ("1:100")),1))))<11 -- scottymelloty ------------------------------------------------------------------------ scottymelloty's Profile: http://www.excelforum.com/member.php...fo&userid=3808 View this thread: http://www.excelforum.com/showthread...hreadid=319314 |
#2
|
|||
|
|||
Assuming the data is in a1 to e1 =SUM(IF(--MID(A1:E1,3,LEN(A1:E1))<10,1,0)) This is an array formula that must be entered with ctrl|shift|enter Note: there were only 2 entries under 10 Lance "scottymelloty" wrote: Hi, Is it possible to count how many top 10's there have been in a row of data that has text with the numbers ie: jp5 ,, jp23 ,, as6 ,, as50 ,, as100 ,, 5 is this data was in a row on the spreadsheet i want it to return that there were 3 entries that were 10 or under someone gave me thsu formula to colour using conditional formatting for a similar problem i had if that helps =--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT ("1:100")),1)),0),100-SUM(--ISERROR(--MID(A1,ROW(INDIRECT ("1:100")),1))))<11 -- scottymelloty ------------------------------------------------------------------------ scottymelloty's Profile: http://www.excelforum.com/member.php...fo&userid=3808 View this thread: http://www.excelforum.com/showthread...hreadid=319314 |
#3
|
|||
|
|||
Thanks for that, i cant get the formula to work but what i didnt tell you is that some cells will be blank and some cells will just have a number in without text so thats probably why, how can i adapt the formula to allow for that Many Thanks. -- scottymelloty ------------------------------------------------------------------------ scottymelloty's Profile: http://www.excelforum.com/member.php...fo&userid=3808 View this thread: http://www.excelforum.com/showthread...hreadid=319314 |
#4
|
|||
|
|||
Sorry another thing i should mention is that this is the full list of letters available before a number some are single letters e u as jp nz vn ct nt and just numbers on there own Thanks. -- scottymelloty ------------------------------------------------------------------------ scottymelloty's Profile: http://www.excelforum.com/member.php...fo&userid=3808 View this thread: http://www.excelforum.com/showthread...hreadid=319314 |
#5
|
|||
|
|||
Assuming that Column A contains your data, enter the following array formula in B1 and copy down: =--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),255) ...confirmed with CONTROL+SHIFT+ENTER. Then, use COUNTIF to get your desired result... =COUNTIF(B:B,"<=10") Hope this helps! scottymelloty Wrote: Hi, Is it possible to count how many top 10's there have been in a row of data that has text with the numbers ie: jp5 ,, jp23 ,, as6 ,, as50 ,, as100 ,, 5 is this data was in a row on the spreadsheet i want it to return that there were 3 entries that were 10 or under someone gave me thsu formula to colour using conditional formatting for a similar problem i had if that helps =--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT ("1:100")),1)),0),100-SUM(--ISERROR(--MID(A1,ROW(INDIRECT ("1:100")),1))))<11 -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=319314 |
#6
|
|||
|
|||
Thanks for all the help guys, much appreciated !!! Will save me a lot of time. -- scottymelloty ------------------------------------------------------------------------ scottymelloty's Profile: http://www.excelforum.com/member.php...fo&userid=3808 View this thread: http://www.excelforum.com/showthread...hreadid=319314 |
#7
|
|||
|
|||
I counted 3: jp5, as6, and 5.
On Mon, 22 Nov 2004 06:43:11 -0800, LanceB wrote: Assuming the data is in a1 to e1 =SUM(IF(--MID(A1:E1,3,LEN(A1:E1))<10,1,0)) This is an array formula that must be entered with ctrl|shift|enter Note: there were only 2 entries under 10 Lance "scottymelloty" wrote: Hi, Is it possible to count how many top 10's there have been in a row of data that has text with the numbers ie: jp5 ,, jp23 ,, as6 ,, as50 ,, as100 ,, 5 is this data was in a row on the spreadsheet i want it to return that there were 3 entries that were 10 or under someone gave me thsu formula to colour using conditional formatting for a similar problem i had if that helps =--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT ("1:100")),1)),0),100-SUM(--ISERROR(--MID(A1,ROW(INDIRECT ("1:100")),1))))<11 -- scottymelloty ------------------------------------------------------------------------ scottymelloty's Profile: http://www.excelforum.com/member.php...fo&userid=3808 View this thread: http://www.excelforum.com/showthread...hreadid=319314 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert text to numbers | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) | |||
I enter numbers and they are stored as text | Excel Discussion (Misc queries) | |||
How to convert Numbers to text | Excel Worksheet Functions | |||
How to Replace numbers and text with numbers only? | Excel Worksheet Functions |