Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Re: Formula to count number of selected rows that meet a certain criteria

News

On June 1, we’re asking you to select a content label when starting a new topic in the Discussions area. Read more to find out why.

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Feb 7, 2019 12:58 PM
(7982 views)

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"))

2 ACCEPTED SOLUTIONS

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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;
```

Jim

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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);
```

Jim

8 REPLIES 8

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

@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

Best

Uday

Uday

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

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?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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;
```

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

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.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

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.

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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);
```

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

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 )
- 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!