topic JMP equivalent of Excel's COUNTIF function in Discussions

JMP equivalent of Excel's COUNTIF function

BHarris — Thu, 22 Aug 2024 14:42:05 GMT

I'm trying to mimic this Excel-type behavior:

	A	B
1	failureType	numFailuresThisType
2	f1	=countif($a$2:$a$999,a2)
3	f2	=countif($a$2:$a$999,a3)
4	f1	=countif($a$2:$a$999,a4)
5	f1	=countif($a$2:$a$999,a5)
6	f2	=countif($a$2:$a$999,a6)
...	...	...

... in JMP. Is this possible as a column formula?

I've tried "Col Sum( :failureType == :failureType[ Row() ] )" but that doesn't work and I don't understand why. Sometimes ":ColumnName" returns a single value, and other times it returns the entire column's-worth of entries and auto-loops over them. Is there a way to tell which it will do, perhaps from the Scripting Index?

Re: JMP equivalent of Excel's COUNTIF function

jthi — Thu, 22 Aug 2024 14:44:34 GMT

Maybe Col Sum of 1 by failuretype?

Col Sum(1, :FailureType)

Here Column 6 has formula Col Sum(1, :age)

Re: JMP equivalent of Excel's COUNTIF function

BHarris — Thu, 22 Aug 2024 14:54:50 GMT

Yep, that'll probably work.

Why didn't my Col Sum(...) work? Is Row() also being updated internally at each step?

Re: JMP equivalent of Excel's COUNTIF function

jthi — Thu, 22 Aug 2024 15:07:47 GMT

Create column with just Row() and you will see what it returns

You can do the same thing for

:failureType[Row()]

and for

:failureType

and finally for

:failureType == :failureType[Row()]

to see what you will end up with (you are comparing current row value of failure type to current row value of failure type with just different syntax, which is almost always 1).

Re: JMP equivalent of Excel's COUNTIF function

jthi — Thu, 22 Aug 2024 15:40:54 GMT

Other option is Col Number()

Col Number(:FailureType, :FailureType)

Re: JMP equivalent of Excel's COUNTIF function

Mark_Bailey — Thu, 22 Aug 2024 16:36:46 GMT

These replies directly answered your question about a function that works like Excel. You can also use Table > Summary, Analyze > Tabulate, and Graph > Graph Builder for summaries without using a formula column if that is where you are headed.

Re: JMP equivalent of Excel's COUNTIF function

BHarris — Thu, 22 Aug 2024 18:06:42 GMT

This formula seems to work as well:

Names Default to Here(1);
n1 = :failureType;
Col Sum( :failureType == n1 )

It seems that within the Col Sum function it is expecting to iterate over lists, similar to doing a for loop in python over zipped variables, but in the 2nd line, it's just getting the value of :failureType from the current row. These kinds of implicit behaviors are incredibly helpful/useful, but only if the user understands how they work (which I would like to).

Re: JMP equivalent of Excel's COUNTIF function

hogi — Thu, 22 Aug 2024 19:18:51 GMT

Wow. Kind of surprising. I didn't expect it to work this way.

How did you find out?

Open( "$SAMPLE_DATA/Big Class.jmp" );

New Column( "ColSum1",
	Formula(
		Col Sum( :age == 12 );
	)
);

New Column( "ColSum2",
	Formula(
		twelve = 12;
		Col Sum( :age == twelve );
	)
);

New Column( "ColSum3",
	Formula(
		myAGE = :age;
		Col Sum( :age == myAGE );
	)
);

New Column( "ColSum4",
	Formula(
		myAGE = :age;
		myname= :name;
		Col Sum( show(myname,myAge,:name,:age,"-");:age == myAGE );
	)
);

Re: JMP equivalent of Excel's COUNTIF function

hogi — Thu, 22 Aug 2024 18:47:41 GMT

related post with some parallels:
but without @BHarris 's magic : )

Re: JMP equivalent of Excel's COUNTIF function

jthi — Thu, 22 Aug 2024 18:57:54 GMT

Even though it looks like it does work, I wouldn't use it (I think it should be fine if it required you to have :failureType as byvar). I would think that it is some sort of unintended behavior (even more than using 1 which shouldn't be allowed either according to JMP support, but let us hope that JMP never breaks that
).

You could report it to JMP Support and try to see if they can give technical explanation about why it works like it does. You can also try using other comparisons in similar manner

Re: JMP equivalent of Excel's COUNTIF function

BHarris — Thu, 22 Aug 2024 19:18:12 GMT

@hogi It's probably not as magical as it seems -- the first argument to Col Sum is an expression, and in this case since n1 is precalculated (likely being set to the value from the current row due to the context), and == expressions seem to return 1 or 0, therefore it's simply adding up a bunch of 1s where the column value equals n1 and 0 otherwise.

What's a little magical to me is that sometimes in column formulas, JMP will iterate over the rows in a column when entered as ":columnName" and other times it only pulls the value from that column in the current row. This has been mysterious to me since the discussion on that other thread.

I'm guessing the Col Number() function is the most appropriate one to use in this case, probably caches more cleanly than this does.

Frankly, I didn't know Col Number() existed. I spent most of my adult life in Excel, so I do wish there was a mapping of Excel functions to JMP functions somewhere.

Re: JMP equivalent of Excel's COUNTIF function

hogi — Thu, 22 Aug 2024 19:49:47 GMT

New Column( "ColSum4",
	Formula(
		myAGE = :age;
		myname= :name;
		Col Sum( show(myname,myAge,:name,:age,"-");:age == myAGE );
	)
);

is quite illustrative. Col cumulative behaves a bit different from how I exected it to behave.
I guessed Col Sum evaluates the arguemt once and then groups/collects the results according to groupBy.

But actually:

Formula is evaluated for every row, in total 40x for rows RWx.
And with every iteration, Col Sum evaluates it's argument 40 times - for every row RWy.
I guess it tries to cache the result and won't evaluate the argument again for other RWx if it can be sure that it will be the same result.

But in the case of Show() or :age = myAGE, it has to evaluate again and again.
-> in total 40 x 40 times.

so ,e.g. for row RWx = 3 (myName = Jane) , it evaluates the argument 40 times.

And for 8 of the 40 RWy, :age == myAge results in 1. So Col Sum is 8 for row 3.
Accordingly, Col Sum is 3 for row 40.

Re: JMP equivalent of Excel's COUNTIF function

hogi — Thu, 22 Aug 2024 19:48:32 GMT

@jthi wrote:
Even though it looks like it does work, I wouldn't use it (I think it should be fine if it required you to have :failureType as byvar). I would think that it is some sort of unintended behavior (even more than using 1 which shouldn't be allowed either according to JMP support, but let us hope that JMP never breaks that
).

I also feel much more confident if a functionality is documented - somehow protected against sudden removal ...

JMP has to find out if it's OK to cache the results. Who knows the logic?

Here, JMP thinks it's OK to use the cached data:

t0 = hptime();
New Column( "test",
	Formula(
		wait(0.1);
		Col Sum( hp time() )
	),
);

current data table() << run formulas();

show((hptime()-t0)/1000000)

Re: JMP equivalent of Excel's COUNTIF function

hogi — Thu, 22 Aug 2024 19:55:09 GMT

@BHarris

@BHarris wrote:
sometimes in column formulas, JMP will iterate over the rows in a column when entered as ":columnName"

Can you give an example?

Re: JMP equivalent of Excel's COUNTIF function

BHarris — Thu, 22 Aug 2024 20:06:13 GMT

Sure, open up BigClass.jmp and add a column with this formula:

Col Sum( :height * :weight, :sex )