How to create a column that counts the total specified values form other columns?

lehoth

It's easy to do this one in Excel with the "countif" function but, How can define a column that counts the total specified values from other columns?

example: I have a table of the total answers, how can I count the right answer for each question?

question	student 1	student 2	right answer	total answer
1	A	A	A	2
2	A	B	B	1
3	C	B	A	0

 

thank you very much.

jthi

Let's start with this table (total_answers is there just to show correct amount):

1. Stack

2. Add formula (Column 6):

3. Tabulate (or summary):

Also take care that the column Data Types and Modeling Types are correct for the data you are modeling.

 

Example table with a bit more advanced formula which I wouldn't suggest using until you learn a bit more about JSL:

 

Names Default To Here(1);

dt = New Table("Untitled 4",
	Add Rows(5),
	Set Header Height(50),
	New Column("question", Numeric, "Ordinal", Format("Best", 12), Set Values([1, 2, 3, 4, 5])),
	New Column("student1", Character, "Nominal", Set Values({"A", "A", "A", "A", "A"})),
	New Column("student2", Character, "Nominal", Set Values({"A", "B", "B", "B", "A"})),
	New Column("student3", Character, "Nominal", Set Values({"A", "B", "C", "C", "A"})),
	New Column("student4", Character, "Nominal", Set Values({"A", "B", "C", "D", "A"})),
	New Column("student5", Character, "Nominal", Set Values({"A", "B", "C", "D", "A"})),
	New Column("right_answer", Character, "Nominal", Set Values({"A", "B", "C", "D", "E"}))
);


//Don' use if you don't have clear understanding what this does! Try first using data table method
//2::5 indicates indices of student result columns
dt << New Column("Total_answers", Numeric, Continuous, 
	<< Formula(
		N Items(Loc(Current Data Table()[Row(), 2::6], :right_answer))
	)
);