topic Re: Countif equivalent? in Discussions

Countif equivalent?

DispersionSpark — Fri, 09 Jun 2023 16:05:13 GMT

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

Re: Countif equivalent?

jthi — Tue, 20 Dec 2022 18:53:00 GMT

Something like this?

Col Sum(1, :Column 1)

Re: Countif equivalent?

DispersionSpark — Tue, 20 Dec 2022 21:43:31 GMT

Exactly - thank you!

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

Re: Countif equivalent?

jthi — Wed, 21 Dec 2022 05:18:40 GMT

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

Re: Countif equivalent?

BHarris — Tue, 01 Aug 2023 03:23:30 GMT

@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.)

Re: Countif equivalent?

jthi — Tue, 01 Aug 2023 18:31:37 GMT

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.

Re: Countif equivalent?

Craige_Hales — Tue, 01 Aug 2023 11:04:23 GMT

@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...)

Re: Countif equivalent?

BHarris — Tue, 01 Aug 2023 14:40:45 GMT

@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.

Re: Countif equivalent?

Craige_Hales — Tue, 01 Aug 2023 16:12:28 GMT

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!

Re: Countif equivalent?

BHarris — Tue, 01 Aug 2023 17:13:20 GMT

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.

Re: Countif equivalent?

Craige_Hales — Tue, 01 Aug 2023 17:54:09 GMT

Close, I think. I was never in that C++ code, but I believe on the very first call (typically for row 1) it preprocesses all the rows to cache all the possible by-value answers. Then all subsequent rows are answered from that by-value cache. (Without the cache it would be an order(N^2) time for N rows, with the cache just order(N) - linear time.)

Re: Countif equivalent?

hogi — Thu, 22 Aug 2024 18:30:15 GMT

@Audrey_Shull If you plan to add @jthi 's example in a future version of Scripting Index or Online Help , please also add a paragraph about the magic of
https://community.jmp.com/t5/Discussions/JMP-equivalent-of-Excel-s-COUNTIF-function/m-p/783500/highlight/true#M96784