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

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

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

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

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

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

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