topic Formula to count number of selected rows that meet a certain criteria in Discussions

Formula to count number of selected rows that meet a certain criteria

BHarris — Thu, 07 Feb 2019 20:58:09 GMT

Is it possible to create a column formula in JMP that counts the number of *selected* rows that meet a certain criteria?

Excel can't do this, but if it did the formula might look something like this: =countif(and(isSelected(),a1:a20=$c$1,b1:b20="red"))

Re: Formula to count number of selected rows that meet a certain criteria

uday_guntupalli — Thu, 07 Feb 2019 22:09:41 GMT

@BHarris ,
May I ask why you are thinking about a column formula ? If you look at the example shown here, there are 4 rows selected in this data table. No matter where you check in your column formula, it will say "4" unless you want to do a cumulative sum of selected rows so it would go gradually from 1 to n.
Here is a very similar post that could address your question if you are looking to use scripting

https://community.jmp.com/t5/Discussions/Count-Rows-Selected-amp-quickly-summarize-categories-under-one/td-p/41932

Re: Formula to count number of selected rows that meet a certain criteria

txnelson — Thu, 07 Feb 2019 22:26:33 GMT

Here is one formula that will work, or it can also be used as an element of a script

dt = Current Data Table();

selRows = dt << get selected rows;
If( N Rows( selRows ) > 0,
	count = 0;
	For( i = 1, i <= N Rows( selRows ), i++,
		count = count + (:age[selRows[i]] == 12 & :sex[selRows[i]] == "F")
	);
,
	count = 0
);
count;

Re: Formula to count number of selected rows that meet a certain criteria

BHarris — Mon, 11 Feb 2019 16:10:40 GMT

I'm really trying to do calculations on the selection -- I've got time-based data, e.g., noise, heat, and vibration vs. time for a number of tests. Sometimes I'll see odd clusters in the data in Graph Builder, which I'll select, then I want to know how long that condition lasted, so I'd like to have another Graph Builder window that can plot count of selected points vs. test id.

Is there a better way to do this?

Re: Formula to count number of selected rows that meet a certain criteria

BHarris — Wed, 20 Feb 2019 23:32:42 GMT

I pasted that text in as a formula for the column, but the column just ended up all dots. I also tried this:

N Row(Current Data Table() << GetSelected())

... which resulted in the same thing. I expected the column to be the number of selected rows, e.g., with 3 rows selected, I expected to see "3" in every cell in the column.

Re: Formula to count number of selected rows that meet a certain criteria

txnelson — Thu, 21 Feb 2019 04:20:27 GMT

Attached is a copy of the JMP Sample Data Table Big Class. I have added in the formula in question for the new column called "Column 6". I am running JMP 14.2 and the formula works for me.

Re: Formula to count number of selected rows that meet a certain criteria

BHarris — Thu, 21 Feb 2019 04:28:12 GMT

I'm running 14.0.0, and that column just shows up as all 1s, regardless of what I select. Is there a recalculate button somewhere?

Out of curiosity, what was wrong with my formula?

N Row(Current Data Table() << GetSelected())

Makes sense to me, but it doesn't work and doesn't seem to offer any meaningful errors.

Re: Formula to count number of selected rows that meet a certain criteria

txnelson — Thu, 21 Feb 2019 06:36:10 GMT

Click on the red triangle next to the name of the data table in the table panel, and select "Rerun Formulas".

If you need to have the formula automatically rerun whenever a change in selections is made, then a simple Row State Handler can be scripted.

names default to here(1);
dt=current data table();

f = function( {a}, dt << rerun formulas(); );
rs = dt << make row state handler(f);

Re: Formula to count number of selected rows that meet a certain criteria

BHarris — Thu, 21 Feb 2019 06:59:58 GMT

Thanks, Jim! It worked!

Slightly rephrased, for those who need to hear things different ways, here's how you have a column that counts the number of selected rows:

Make a new column with formula: N Rows( Current Data Table() << get selected rows )
1. Note, this formula won't automatically update when the selection changes, which is why we do step #2...
Make a new script (in red triangle menu in top left of data table) with:

Names Default To Here( 1 );
dt = Current Data Table();
f = Function( {a},
dt << rerun formulas()
);
rs = dt << make row state handler( f );

... which basically says, whenever the row states change, run function "f", which takes "a" as an argument (which gets ignored), and tells dt (the current data table) to re-run its formulas. Then run this script once.

Jim's answer shows how to extend this to only count selected rows that meet some other criteria, which was part of the original question.

Thx again!