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
- :
- Discussions
- :
- Formula Countif column

Topic Options

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

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

Feb 20, 2012 10:56 AM
(8394 views)

I am trying to use a condition to create a formula on a column.

The datatable example is:

Variable 1

CASSYGDGGTGELFF

CATSRSSGYEQYF

CASSPAAGAAYEQYF

CASSPAAGAAYEQYF

CASSVGSDAYEQYF

CASSVGGVSYNEQFF

CASSVSGSAYEQYF

CASSPAAGAAYEQYF

And I would like to create a variable that would be the count of variable 1 for each row.

Variable 1 Count of Variable 1

CASSYGDGGTGELFF 1

CATSRSSGYEQYF 1

CASSPAAGAAYEQYF 3

CASSPAAGAAYEQYF 3

CASSVGSDAYEQYF 1

CASSVGGVSYNEQFF 1

CASSVSGSAYEQYF 1

CASSPAAGAAYEQYF 3

In excel I would use

B1=countif(A:A,A1)

...

B8=countif(A:A,A8)

I know how to do it through tabulate and I can get around this problem in different ways. However, this is one brick of a more complex formula that I would like to implement in JMP.

Thanks,

Jorge Almeida

1 ACCEPTED SOLUTION

Accepted Solutions

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

Feb 27, 2012 6:53 AM
(11973 views)
| Posted in reply to message from jorgeramosdealm 02/21/2012 11:19 AM

There is a JMP counterpart to countif(): Col Number(col, <optional bycol>). However, it only works for numeric columns so character varibles has to be converted to numbers. Missing values will yield zero.

Try this column formula

New Column**(** "Count of Variable 1",

Numeric,

Continuous,

Formula**(**

Col Number**(** Hex To Number**(** Char To Hex**(** :Variable 1 **)** **)**, :Variable 1 **)**

**)**

**)**

8 REPLIES

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

Feb 20, 2012 2:32 PM
(6766 views)
| Posted in reply to message from jorgeramosdealm 02/20/2012 01:56 PM

I'm not a JMP user, thus probably shouldn't be attempting to answer your question. However, upon looking at the JMP documentation, it appears that one can use SQL in JMP.

Using the combination of count and group by statements, with SQL, would do the same thing and much easier than having all the individual statements in Excel.

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

If that would do it, sure. However, I am not very experienced in JMP and I have not used the SQL scripting. If anyone would be able to do it through the SQL and show it to me, I will be extremely thankful. Thanks art.

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

Feb 21, 2012 7:27 AM
(6766 views)
| Posted in reply to message from jorgeramosdealm 02/20/2012 01:56 PM

JMP does not have SQL as part of JSL. There are SQL-like features, like select where, table joins, updates, etc., but you can't issue SQL commands like you can in SAS. That would be a really great feature (hint hint!).

Anyway I've needed to do something similar and used tabulate and then joined the results back to the original table. I'm not sure a column formula would be easy to implement, and the performance might not be good for large tables.

Here's how to do it using tabulate and update. The table **Untitled 8** contains your original data in a column called **Variable 1**.

dt = data table**(**"Untitled 8"**)**;

tmp_tab = dt << Tabulate**(**

Show Control Panel**(** **0** **)**,

Add Table**(** Row Table**(** Grouping Columns**(** :Variable 1 **)** **)** **)**, invisible

**)**;

tmp_tbl = tmp_tab << Make Into Data Table;

tmp_tab << close window;

Data Table**(** "Untitled 8" **)** << Update**(**

With**(** tmp_tbl **)**,

Match Columns**(** :Variable 1 = :Variable 1 **)**,

Add Columns from Update table**(** :N **)**

**)**;

close**(**tmp_tbl, nosave**)**;

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

Thanks PMroz, that was exactly what I did. I am trying to transfer all my data analysis from excel to JMP and there are some things that require this kind of workaround. Anyway, if there would be some column formula that could tackle this problem, it would be great. I will keep this discussion open for a little longer in order to see if there are any other suggestions.

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

Feb 27, 2012 6:53 AM
(11974 views)
| Posted in reply to message from jorgeramosdealm 02/21/2012 11:19 AM

There is a JMP counterpart to countif(): Col Number(col, <optional bycol>). However, it only works for numeric columns so character varibles has to be converted to numbers. Missing values will yield zero.

Try this column formula

New Column**(** "Count of Variable 1",

Numeric,

Continuous,

Formula**(**

Col Number**(** Hex To Number**(** Char To Hex**(** :Variable 1 **)** **)**, :Variable 1 **)**

**)**

**)**

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

That formula worked. Thank you very much. I was able to integrate it in my other more complex formula as in:

"Variable 1" "Name" "SharedVariable 1" "Count of shared"

CASSYGDGGTGELFF John Private 1

CATSRSSGYEQYF John Private 1

CASSPAAGAAYEQYF John Shared 3

CASSPAAGAAYEQYF Albert Shared 3

CASSVGSDAYEQYF Albert Private 1

CASSVGGVSYNEQFF Albert Private 1

CASSVSGSAYEQYF Albert Private 1

CASSPAAGAAYEQYF Michael Shared 3

New Column**(** "SharedVariable1",

Character,

Nominal,

Formula**(**If**(**ColNumber**(** Hex To Number**(** Char To Hex**(** :Variable 1 **)** **)**, :Variable 1 **)** > Col Number**(** Hex To Number**(** Char To Hex**(** :Variable 1 **)** **)**, :Variable 1 || :Name **)**, Char**(**"Shared"**)**, Char**(**"Private"**)))**

**)**;

New Column**(** "Count of shared",

Numeric,

Continuous,

Formula**((**ColNumber**(** Hex To Number**(** Char To Hex**(** :Variable 1 **)** **)**, :Variable 1 **)** - Col Number**(** Hex To Number**(** Char To Hex**(** :Variable 1 **))**, :Variable 1 || :Name **))**+**1)**

**)**

One question:

Col Number**(** Hex To Number**(** Char To Hex**(** :Variable 1 **)** **) ****is to create a countable variable. To specificy what to count is after the comma on the:**

** Col number(Column with a numeric variable, Condition to count)**

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

Feb 27, 2012 8:58 AM
(6766 views)
| Posted in reply to message from jorgeramosdealm 02/27/2012 11:38 AM

Yes, the optional "by column" is after the comma. With a by column you get the counts of each value in that column. Whithout a by column you would get the total number of non-missing values, including multiples.

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

Aug 4, 2016 2:36 PM
(6766 views)
| Posted in reply to message from jorgeramosdealm 02/20/2012 01:56 PM

If anyone comes here looking for the JSL script to simply store a variable with the count of cells with a certain criteria, like I did, here's what i came up with that worked for me:

dt = current data table ();

CountCondition1 = nrows ( dt << get rows where (:[Column with condition] [LOGICAL CONDITION]));

...

CountConditionX = nrows ( dt << get rows where (:[Column with condition] [LOGICAL CONDITION]));

So, for a data table with the set up:

Name Number of Incidences

Apples 3

Oranges 4

Bananas 0

Grapes 0

and you want to know, for instance, what % of fruit has a non-zero value:

CountNonZeros = nrows( dt << get rows where (:Number of Incidences > 0));

// CountNonZeros variable becomes value 2

CountTotal = nrows (dt);

// CountTotal becomes value 4

PercentNonZero = CountNonZeros/CountTotal*100

// PercentNonZero becomes 50