# Thread: Count all values of each row from smaller to larger Thanks:  6 Post #5201432 (1)Post #5201045 (1)Post #5327281 (1)Post #5201072 (1)Post #5327512 (1) Likes:  5 Post #5201432 (2)Post #5327281 (1)Post #5327512 (1)Post #5201045 (1)

1. ## Count all values of each row from smaller to larger

Hello,

I want to count from smaller to larger valve of each row and put them in one cell separated by vertical bar. Is it possible?

As per example below

ABCDEFGHIJKLMNOPQRS
1
2
3
4
5n1n2n3n4n5n6n7n8n9n10n11n12n13n14Count Smaller to Larger
6
7100010101100106 | 8
8211021002011006 | 5 | 3
9300030103100108 | 3 | 3
10400140200210008 | 2 | 2 | 2
11010200300321008 | 2 | 2 | 2
12121311001000016 | 6 | 1 | 1
13200400010010109 | 3 | 1
14011501000100208 | 4 | 1 | 1
15000612101201315 | 5 | 2 | 1 | 1
160201101000000010 | 3 | 1
17101020201111105 | 7 | 2
182000010000000111 | 2
19000060150260137 | 2 | 1 | 1 | 1 | 2
20111070260301205 | 4 | 2 | 1 | 1 | 1
210020000100400011 | 1 | 1 | 1
220010000000021011 | 2 | 1
23012111011110204 | 8 | 2 |
24123222000201006 | 2 | 5 | 1
25034033101000116 | 4 | 3 | 1
26105040210100207 | 3 | 2 | 1 | 1
27210001321011006 | 5 | 2 | 1
28001110402022106 | 4 | 3 | 1

Sheet1

Thank you all

Excel 2000
Regards,
Moti

2. ## Re: Count all values of each row from smaller to larger

Originally Posted by motilulla
Hello,
I want to count from smaller to larger valve of each row and put them in one cell separated by vertical bar. Is it possible?
Here is clarification what I mean "from smaller to larger valve" count 1st 0, then 1, then 2, then 3, and so on....

For example row 7 ranges D7:Q7.........0=6, 1=8...............................so far result S7= 6 | 8

For example row 8 ranges D8:Q8.........0=6, 1=5, 2=3.......................so far result S8= 6 | 5 | 3
For example row 9 ranges D9:Q9.........0=8, 1=3, 3=3.......................so far result S9= 8 | 3 | 3
For example row 10 ranges D10:Q10....0=8, 1=2, 2=2, 4=2.............so far result S10= 8 | 2 | 2 | 2

May this help

Regards,

Moti

3. ## Re: Count all values of each row from smaller to larger

Hi,

I don't agree with your results for rows 7, 13 and 18, but try this array formula** in S7:

=SUBSTITUTE(TEXT(NPV(-0.9,IFERROR(1/(1/COUNTIF(D7:Q7,{9,8,7,6,5,4,3,2,1,0}))/10,"")),REPT("# | ",COUNT(1/FREQUENCY(D7:Q7,D7:Q7))))," | ","",COUNT(1/FREQUENCY(D7:Q7,D7:Q7)))

and copied down.

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

4. ## Re: Count all values of each row from smaller to larger

N.B. I should've mentioned that I presumed from your data that the only possible entries in a given row are the single-digit numbers from 0-9.

Regards

5. ## Re: Count all values of each row from smaller to larger

Originally Posted by XOR LX
Hi,
I don't agree with your results for rows 7, 13 and 18, but try this array formula** in S7:
Hello XOR LX, sorry you are correct row 7 should be = 8 | 6, row 13 should be = 9 | 3 | 1 | 1 and row 18 should be = 11 | 2 | 1.

Thank you for help and giving a formula solution but it is not working with my version Excel 2000. May it is good for earlier versions

Kind Regards,

Moti

6. ## Re: Count all values of each row from smaller to larger

Originally Posted by XOR LX
N.B. I should've mentioned that I presumed from your data that the only possible entries in a given row are the single-digit numbers from 0-9.

Regards
XOR LX, in this given example there are only "single-digit numbers from 0-9" but it could be from 0-99

7. ## Re: Count all values of each row from smaller to larger

What results do you get? Are you getting the #NAME ? error? If so, can you confirm that it is the NPV function which is not available in your version of Excel?

Regards

8. ## Re: Count all values of each row from smaller to larger

Originally Posted by XOR LX
What results do you get? Are you getting the #NAME ? error? If so, can you confirm that it is the NPV function which is not available in your version of Excel?

Regards
I am getting #NAME Please can you guide me how to check where I can find NPV ?

9. ## Re: Count all values of each row from smaller to larger

NPV is available in 2003, but don't know about 2000
However IFERROR is not available in 2000

10. ## Re: Count all values of each row from smaller to larger

Originally Posted by motilulla
I am getting #NAME Please can you guide me how to check where I can find NPV ?
My mistake. Of course it's the IFERROR (and potentially also NPV, though I thought that was available in 2000), which is causing the error.

Originally Posted by motilulla
XOR LX, in this given example there are only "single-digit numbers from 0-9" but it could be from 0-99
Ah, in that case unfortunately this will not be possible using worksheet formulas alone with your version of Excel. You'll require some VBA. Are you ok with that?

Regards

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•