# Excel: Sort row base on multiple columns

Let me get this straight. I have the following data:

I want to sort it base on "x", the result should be

the row that contains more "x" and has the first "x" to the 10th will be at the top, it's like sorting from large to small amount of "x".

how can I possibly do that?

I know how to sort one column base on integers and a-z vice-versa but this one is different for me.

• Add a helper column that contains a count of the Xs, then sort on that. Is there a rule for when multiple rows have the same count? Apr 24 '18 at 7:43

In column `M` put a title, for example COUNT. In cell `M2` put the formula `=COUNTIF(C2:L2,"X")` and copy down the formula through the column.

Now, in column `M` you have the count of X in the row.

In column `N` put a title, for example SUM. In cell `N2` put the formula `=SUMIF(C2:L2;"X";\$C\$1:L1)` and copy down the formula through the column.

Now, in column `N` you have the WEIGHT of the X's for each row.

Go to sort and in the dialog choose COUNT Largest to Smallest and another level with the conditions SUM or whatever name you put in column `N` and Smallest to Largest, like this:

• It works, but there's a problem. as you can see, the column "x" is from 1 to 10. I want to prioritize those columns that contains "x" from 1 to 10. e.g. `C:1` from `row:2` has only one "x" and `c:2` and `c:3` from `row:2` contains "x", so the `row:1` will be prioritize to be at the top of `row:2` because `c:1` has the "x". Apr 24 '18 at 9:01
• @KrixMuxDeadpool I corrected my answer to help you with the condition Apr 24 '18 at 10:06
• I Got a problem with your formula `=SUMIF(C2:L2;"X";\$C\$1:L1)` please check this link to see the screenshot lh3.googleusercontent.com/… Apr 25 '18 at 6:22
• I'm confused with what you said `Go to sort and in the dialog choose COUNT Largest to Smallest and another level with the conditions SUM or whatever name you put in column N and Smallest to Largest`, what do you mean by `another level with the conditions SUM`? did you mean, sort the value of column COUNT from Largest to Smallest and also the column SUM? Apr 25 '18 at 6:37
• @KrixMuxDeadpool Check the image that I added to the answer Apr 25 '18 at 7:40

Create a helper column. For example, in M2 enter `=Countif(C2:L2,"x")` and then fill down. Now sort by Column M.

You might be willing to try the following:

1. In column M, prepare a "helper column" which counts the number of x's as described in previous answers.
2. In column N, please enter `=MATCH("x", C2:L2, 0)` which will give you the position of the first x in each row. Drag down to extend formula to all rows involved and/or adapt suitably to your locale.
3. Now, you may order by two levels: first, column M, descending; second, column N, ascending.

Hope this fits your needs.

Kind regards.

You need a Helper Column to Sort the Data on basis of most columns filled per row.

Write this formula in M2 `=COUNTA(C2:L2)` & fill it down till row 6.

Then follow these steps:

• Select A1:M6.
• Click Sort & Filter.
• Click Custom Sort.
• Select Helper Column, Values for Sort on and Smallest to Largets As order.
• Finally Click Ok.