BookmarkSubscribeRSS Feed
m4mazzotti

Community Trekker

Joined:

Apr 17, 2015

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?

1 ACCEPTED SOLUTION

Accepted Solutions
michael_jmp

Staff

Joined:

Jun 23, 2011

Solution

Re: SumIf equivalent in Column Formula

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

Michael Crotty
Sr Statistical Writer
JMP Development
1 REPLY
michael_jmp

Staff

Joined:

Jun 23, 2011

Solution

Re: SumIf equivalent in Column Formula

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

Michael Crotty
Sr Statistical Writer
JMP Development