Countif equivalent?

[#1]

I am trying to find a JMP formula function equivalent to Excel’s Countif function, that points to a column and returns the number of rows that are the same as the value in the current row, as you would get if you did a Summary on that row.

In other words, if I have a 1000-line table, and column Color is set to "Purple" in 27 rows, I want a formula for a new row that returns 27 for those rows (and the applicable value for the other rows)

(I’m sure this is in here, but **bleep** if I can find it)

JMP 17, macOS

[#2 · Accepted solution]

Something like this?

Col Sum(1, :Column 1)

[#3]

Exactly - thank you!

(and the JMP help text on Col Sum sure is unintuitive...)

[#4]

Which help text do you mean and what is unintuitive with it? Col Sum(name,<By var, ...>) 

Also from Scripting Index (found from JMP's Help menu) you can get executable demo scripts which can sometimes be helpful

[#5]

@jthi :  The help text says the first argument to Col Sum() is "xCol", but you just have a "1" in there.  What is JMP doing with that number 1 here?

(This is very useful, we had this question today too.)

[#6]

It is using 1 for the value of the row instead of a specific value found from some column's row.

Edit: In this case I think using Col Number(:Column 1, :Column 1) should also work.

[#7]

@Audrey_Shull  maybe the @jthi  example can be added to the scripting index with an explanation, something like "The first argument of the column formula is an expression that is evaluated for each row. It is usually a column, which sums the values of the column, but in this example it is a constant 1 which sums to the count of the rows. That is useful in this example because the second argument is the by-column, which makes the sums be for each by-value."

Adding the countif keyword might help someone find it in the future (I'm not excel proficient, so maybe look up countif first...)

[#8]

@Craige_Hales  So, if the first entry is a column object, the Col Sum() function steps over the values in the column (honoring the byVar setting) summing the values, but if the first entry is just a number it just uses that? 

Can the first entry be any expression that returns a value?

Can it also return a list that gets stepped over similar to providing a column object?  What if the length of that list doesn't match the length of the byVar list?

This internal behavior of JMP is fascinating and I'd love to learn more about it, but I can't find anything in the docs to help me develop a mental model of what it's doing in cases like this...

Note, countif is an excel function that counts all items of a range of cells that meet some criteria.  I don't think Col Sum() does exactly that, there doesn't seem to be a way to use it to count the number of cases where col1 > 7 and col2 == 15, for example.

[#9]

Think of the first argument as an expression. An expression can be

• 42
• :height
• :height *2 > :weight
• if ( :height *2 > :weight, age, 99 )
• if ( :height>60 & :weight> 100, 1, 0 )

All of those are evaluated in the context of the current row, so using the current row's height and weight and age values. The > operator returns 1 for true and 0 for false, so that can be summed as well. The if evaluates to either age or 99 on each row and that gets summed. And just using :height also gets evaluated to return the current row's value.

I admit, I looked at @jthi  example for a minute before I got it. If you build this into code, add a comment for whoever has to maintain it next!

[#10]

That makes sense, but then the Col Sum() function is iterating over something... 

I imagine it's iterating over the rows that match the current value in the byVar columns, evaluating the expression for each one, then adding up all the results of those evaluations.

Do I have that right?  If so, that wording would've been very helpful for me in the Col Sum() scripting index documentation.