How to write a formula that counts number of rows that are not excluded?

[#1]

I am trying to write a simple formula that will count the number of rows in my datatable that are not excluded. I want the total to be updated as I change row states using the graph builder. In another part of the formula I was able to successfully use the Excluded () function, e.g. 

<jsl> X = Col Sum( :MG1_2520_LH_Setting, Excluded() );

However, I am using N Rows() to count the total number of rows and cannot see how to modify this function to only count the number of non-excluded rows. 

[#2]

Hi,

I have an ugly but usable solution: create a Dummy column populated with the value = 1 and then use the formula as you showed in JSL:

Col Sum(:Dummy, Excluded())

There is probably a much more elegant solution, so you may want to wait to accept the solution.

Best,

TS

[#3 · Accepted solution]

You cannot use Col sum in "plain" JSL code to assign the result to a variable.
It'a a cool aggregation function which can be used in Column Formulas.

Col Sum sums up the individual entries of a column - and as @Thierry_S indicates, you need a column with ones to count the rows. But actually, you don't need a real column, instead of a column, you can also specify "1".
If you just want to count rows which are NOT excluded, use:

New Column( "non excluded",Formula( Col Number( If( !Excluded(), 1 ) ) ))

The complicated syntax is necessary because by default the Col ... aggregation doesn't respect the excluded state - it uses all values of the column and therefore counts all rows.

[#4]

There are other approaches in JMP which automatically take into account which rows are excluded and which ones are not.

If you don't need a new column but just a single value, you can use a summary table with a transform column:

Summary( N( Transform Column( "one", Formula( !Excluded() ) ) ));

If you need the value to process it further via JSL, Summarize provides an easy alternative:

Names Default to Here(1);
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

dt << Select Rows( 5::12 ) <<Exclude;
summarize ( count = count(:height));	
count

[#5]

I was able to use the code as below to achieve what I needed. The other solutions worked as well but this was the easiest to implement for me. Thanks!

Col Number( If( !Excluded(), 1 ) )