dumb excel question

Brain = in another place today

How do I make a spreadsheet that will function as a product/feature comparison checklist? IE, I list the items to be compared in column A, then all the features in columns B thru K. If a product is lacking a feature, then I'll leave that column blank; if it has a feature, it will have an x(or another value if x won't work) in the cell.

What I'm failing at now is, how would I make column L add up all the values from B>K, and then sort the rows by whatever has the most features?

So if it looks like this:

product a | x | x | _ | x |
product b | x | _ | _ | x |
product c | x | x | x | x |

then it will get sorted so product c is at the top of the list/rows, followed by product a and then b. If there is a template that already does this that would be very useful too.

help!

 

use the function below
=COUNTIF(B4:K4,"x")

 

the function adds up the x's but does not sort the rows, which is where the problem lies

 

if there is say 20 products
Select the rows and columns from A1 to L20
then go to Data menu and click on Sort
then select sort by column L in decending option checked and click OK.

 

any way to apply the function to the entire column without having to c/p it all the way down?

 

Say you have this formula in cell L4
=COUNTIF(B4:K4,"x")
And you want that formula in cells L4 to L99

Method1:
- click cell L4
- click control "c" (at same time, to copy)
- highlight cells L5 to L99 with mouse
- click control "v" (at same time, to paste)

Method2:
- click cell L4
- click control "c" (at same time, to copy)
- Hold shift, while pushing down arrow, this selects multiple cells
- once cells L4 to L99 are selected, click control "v" (at same time to paste)

Also,
say you want to paste the formula into all cells under L4, cells L4 to L1048576
- click cell L4
- click control "c" (at same time, to copy)
- Hold shift, control, while pushing down arrow, this selects all cells in that direction
- click control "v" (at same time to paste)

 

or
double click on bottom right corner of cell L4

 

Hahahaha,
Knew I was forgetting one.

 

• Record Macro.
• Select all your data and do the Sort (Descending) on the row L 'feature count' value.
• Stop Recording Macro (& assign meaningful name)
• Add a command button somewhere on the worksheet (below/to the side/wherever)
• Assign the macro name to the command button (and edit the text on the button to something meaningful)

Now all you have to do is hit the button and the rows will be reorganised with the highest scorer in row 2 (after row 1, your column headings). Easy.

Protip: when you select your data, select many blank rows under your last entry for future-proofness (so when you add more rows with data, they are included in what the macro sorts - I've learnt the hard way that VBA references do not update the way Excel cell formula references do e.g. with insert/delete rows)