cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
m4mazzotti
Level I

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

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

View solution in original post

1 REPLY 1

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