topic Re: Formula to count number of selected rows that meet a certain criteria in Discussions
https://community.jmp.com/t5/Discussions/Formula-to-count-number-of-selected-rows-that-meet-a-certain/m-p/119518#M40002
<P>I pasted that text in as a formula for the column, but the column just ended up all dots. I also tried this:</P><P> </P><PRE><CODE class=" language-jsl">N Row(Current Data Table() << GetSelected())</CODE></PRE><P>... 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.</P>Wed, 20 Feb 2019 23:32:42 GMTBHarris2019-02-20T23:32:42ZFormula to count number of selected rows that meet a certain criteria
https://community.jmp.com/t5/Discussions/Formula-to-count-number-of-selected-rows-that-meet-a-certain/m-p/109169#M39624
<P>Is it possible to create a column formula in JMP that counts the number of *selected* rows that meet a certain criteria? </P><P> </P><P>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"))</P>Thu, 07 Feb 2019 20:58:09 GMThttps://community.jmp.com/t5/Discussions/Formula-to-count-number-of-selected-rows-that-meet-a-certain/m-p/109169#M39624BHarris2019-02-07T20:58:09ZRe: Formula to count number of selected rows that meet a certain criteria
https://community.jmp.com/t5/Discussions/Formula-to-count-number-of-selected-rows-that-meet-a-certain/m-p/109220#M39625
<P><LI-USER uid="12313"></LI-USER> , <BR /> 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. <BR /> Here is a very similar post that could address your question if you are looking to use scripting</P><P><A href="https://community.jmp.com/t5/Discussions/Count-Rows-Selected-amp-quickly-summarize-categories-under-one/td-p/41932" target="_blank" rel="noopener">https://community.jmp.com/t5/Discussions/Count-Rows-Selected-amp-quickly-summarize-categories-under-one/td-p/41932</A><BR /><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 537px;"><img src="https://kvoqx44227.i.lithium.com/t5/image/serverpage/image-id/15452i975C4749890E0D71/image-dimensions/537x448?v=1.0" width="537" height="448" title="image.png" alt="image.png" /></span></P>Thu, 07 Feb 2019 22:09:41 GMThttps://community.jmp.com/t5/Discussions/Formula-to-count-number-of-selected-rows-that-meet-a-certain/m-p/109220#M39625uday_guntupalli2019-02-07T22:09:41ZRe: Formula to count number of selected rows that meet a certain criteria
https://community.jmp.com/t5/Discussions/Formula-to-count-number-of-selected-rows-that-meet-a-certain/m-p/109422#M39626
<P>Here is one formula that will work, or it can also be used as an element of a script</P>
<PRE><CODE class=" language-jsl">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;</CODE></PRE>Thu, 07 Feb 2019 22:26:33 GMThttps://community.jmp.com/t5/Discussions/Formula-to-count-number-of-selected-rows-that-meet-a-certain/m-p/109422#M39626txnelson2019-02-07T22:26:33ZRe: Formula to count number of selected rows that meet a certain criteria
https://community.jmp.com/t5/Discussions/Formula-to-count-number-of-selected-rows-that-meet-a-certain/m-p/109856#M39736
<P>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.</P><P> </P><P>Is there a better way to do this?</P>Mon, 11 Feb 2019 16:10:40 GMThttps://community.jmp.com/t5/Discussions/Formula-to-count-number-of-selected-rows-that-meet-a-certain/m-p/109856#M39736BHarris2019-02-11T16:10:40ZRe: Formula to count number of selected rows that meet a certain criteria
https://community.jmp.com/t5/Discussions/Formula-to-count-number-of-selected-rows-that-meet-a-certain/m-p/119518#M40002
<P>I pasted that text in as a formula for the column, but the column just ended up all dots. I also tried this:</P><P> </P><PRE><CODE class=" language-jsl">N Row(Current Data Table() << GetSelected())</CODE></PRE><P>... 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.</P>Wed, 20 Feb 2019 23:32:42 GMThttps://community.jmp.com/t5/Discussions/Formula-to-count-number-of-selected-rows-that-meet-a-certain/m-p/119518#M40002BHarris2019-02-20T23:32:42ZRe: Formula to count number of selected rows that meet a certain criteria
https://community.jmp.com/t5/Discussions/Formula-to-count-number-of-selected-rows-that-meet-a-certain/m-p/126755#M40011
<P>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.</P>
<P> </P>
<P> </P>Thu, 21 Feb 2019 04:20:27 GMThttps://community.jmp.com/t5/Discussions/Formula-to-count-number-of-selected-rows-that-meet-a-certain/m-p/126755#M40011txnelson2019-02-21T04:20:27ZRe: Formula to count number of selected rows that meet a certain criteria
https://community.jmp.com/t5/Discussions/Formula-to-count-number-of-selected-rows-that-meet-a-certain/m-p/126934#M40012
<P>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?</P><P> </P><P>Out of curiosity, what was wrong with my formula? </P><P> </P><PRE class=" language-jsl"><CODE class=" language-jsl"><SPAN class="token function">N Row</SPAN><SPAN class="token punctuation">(</SPAN><SPAN class="token function">Current Data Table</SPAN><SPAN class="token punctuation">(</SPAN><SPAN class="token punctuation">)</SPAN> <SPAN class="token operator"><</SPAN><SPAN class="token operator"><</SPAN><SPAN class="token messages"> GetSelected</SPAN><SPAN class="token punctuation">(</SPAN><SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">)</SPAN></CODE></PRE><P> </P><P>Makes sense to me, but it doesn't work and doesn't seem to offer any meaningful errors.</P>Thu, 21 Feb 2019 04:28:12 GMThttps://community.jmp.com/t5/Discussions/Formula-to-count-number-of-selected-rows-that-meet-a-certain/m-p/126934#M40012BHarris2019-02-21T04:28:12ZRe: Formula to count number of selected rows that meet a certain criteria
https://community.jmp.com/t5/Discussions/Formula-to-count-number-of-selected-rows-that-meet-a-certain/m-p/130009#M40014
<P>Click on the red triangle next to the name of the data table in the table panel, and select "Rerun Formulas".</P>
<P> </P>
<P>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.</P>
<PRE><CODE class=" language-jsl">names default to here(1);
dt=current data table();
f = function( {a}, dt << rerun formulas(); );
rs = dt << make row state handler(f);</CODE></PRE>Thu, 21 Feb 2019 06:36:10 GMThttps://community.jmp.com/t5/Discussions/Formula-to-count-number-of-selected-rows-that-meet-a-certain/m-p/130009#M40014txnelson2019-02-21T06:36:10ZRe: Formula to count number of selected rows that meet a certain criteria
https://community.jmp.com/t5/Discussions/Formula-to-count-number-of-selected-rows-that-meet-a-certain/m-p/130528#M40015
<P>Thanks, Jim! It worked!</P><P> </P><P>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:</P><P> </P><OL><LI>Make a new column with formula: N Rows( Current Data Table() << get selected rows )<OL><LI>Note, this formula won't automatically update when the selection changes, which is why we do step #2...</LI></OL></LI><LI><SPAN style="font-family: inherit;">Make a new script (in red triangle menu in top left of data table) with:</SPAN></LI></OL><DIV><PRE><CODE class=" language-jsl">Names Default To Here( 1 );
dt = Current Data Table();
f = Function( {a},
dt << rerun formulas()
);
rs = dt << make row state handler( f );</CODE></PRE>... 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.</DIV><DIV> </DIV><DIV>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.</DIV><DIV> </DIV><DIV>Thx again!</DIV><P> </P>Thu, 21 Feb 2019 06:59:58 GMThttps://community.jmp.com/t5/Discussions/Formula-to-count-number-of-selected-rows-that-meet-a-certain/m-p/130528#M40015BHarris2019-02-21T06:59:58Z