This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

- JMP User Community
- :
- Discussions
- :
- How to get Col Sum of a row in a conditional statement?

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

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.

Submit your abstract to the call for content for Discovery Summit Americas by April 23. Selected abstracts will be presented at Discovery Summit, Oct. 21- 24.

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
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content

2 weeks ago
(201 views)

Hi all,

I have a column where I need to filter out some values to get the sum of columns (using Col Sum). Is it possible to get the col sum according to the condition? For example, every 0 code will be excluded from the computation:

Code | Qty | Total Count | Total % (Col Sum (qty)/total count) |

0 | 50 | 250 | - |

0 | 25 | 250 | - |

1 | 60 | 250 | 205/250 x 100 = 82% |

6 | 67 | 250 | 205/250 x 100 = 82% |

3 | 78 | 250 | 205/250 x 100 = 82% |

I tried using an If statement, but the code with 0 values still gets included when using the Col Sum formula

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content

You need two if-statements, one to avoid writing values to Code = 0 rows and one to perform the calculation

```
If(Code != 0,
Col Sum(If(:Code == 0, ., :Qty))
,
.
);
```

` `

-Jarmo

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content

You need two if-statements, one to avoid writing values to Code = 0 rows and one to perform the calculation

```
If(Code != 0,
Col Sum(If(:Code == 0, ., :Qty))
,
.
);
```

` `

-Jarmo

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content

Hi @UserID16644

Conditional formula is a bit tricky in JMP.

As @jthi mentioned, you need 2 IFs.

You can also add formula column as below;

-Add 1st IF = column sum if code not 0

-Add 2nd IF = display result only if code not 0

Result:

JSL Script (refer workflow builder / log)

```
Names Default To Here( 1 );
//Change column info: Total % (Col Sum Qty/Total Count)
Data Table( "dt1" ):Column 4 << Set Name( "Total % (Col Sum Qty/Total Count)" )
<< Set Formula(
If( !:Code == 0,
Col Sum( If( !:Code == 0, :Qty, . ) ) / :Total Count
)
);
//Change column format: Total % (Col Sum Qty/Total Count)
Data Table( "dt1" ):"Total % (Col Sum Qty/Total Count)"n <<
Format( "Percent", 12, 2 );
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content

Maybe I'm missing what you're asking, but this might do what you want with a simple Col Sum():

`Col Sum( (:Code > 0) * :Qty, :Code > 0 )`

":Code > 0" becomes 0 or 1 depending on whether it's zero or not, multiplied times Qty gives you the number you're looking for, then grouping cases where :Code >0 separate from the Code == 0 cases.

- © 2024 JMP Statistical Discovery LLC. All Rights Reserved.
- Terms of Use
- Privacy Statement
- About JMP
- JMP Software
- JMP User Community
- Contact