turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Does JMP have a SumIf equivalent in Column Formula...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 17, 2015 9:51 AM
(1428 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 17, 2015 10:00 AM
(1045 views)

Solution

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

Sr Statistical Writer

JMP Development

1 REPLY

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 17, 2015 10:00 AM
(1046 views)

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

Sr Statistical Writer

JMP Development