Does JMP have a SumIf equivalent in Column Formula Editor?I will explain what I am trying to achieve with an example
| ID |
Value |
Sum |
|---|
| 1 |
5 |
13 |
| 1 |
6 |
13 |
| 2 |
9 |
9 |
| 3 |
1 |
1 |
| 1 |
2 |
13 |
I have a column with a bunch of IDs, they can repeat. Then a have a second column with different values for those IDs.
On the 3rd column, I am trying to have a formula to sum all the Values from all the rows that have the same ID as the current row.
For Row 1 we have, ID = 1, so 5+6+2= 13
Any ideas on how to do that?
Re: SumIf equivalent in Column Formula<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />Using the ColSum() function with a second argument to specify a By variable should work. The script below recreates your table, using a formula for the third column:
New Table( "Data",
Add Rows( 5 ),
New Column( "ID",
Numeric,
"Continuous",
Set Values( [1, 1, 2, 3, 1] )
),
New Column( "Value",
Numeric,
"Continuous",
Set Values( [5, 6, 9, 1, 2] )
),
New Column( "SumIf",
Numeric,
"Continuous",
Formula( Col Sum( :Value, :ID ) )
)
)
Hope that helps,
Michael
