Sum Columns based on Certain Criteria

[JMM2022]

Hi All,

I can't seem to figure out a way to solve something I have been working on.  In the attached workflow, I have used the Summarize tool to sum my amount column. I used this as I am grouping the data mainly by the first column (Pairoff No) and within that group if the Pairoff No is the same and the ID No. Column is also the same this collapses my Amount into 1 row which is exactly what I want.  

The issue I cannot solve is I need to also create a Total Amount column based on specific criteria.  For example, when the pairoff no is the same, and the Trans Code Description column has multiple rows that are "Buy" but with different values in the ID No. column, I would like to sum those values.  In my attached input, the Pairoff No set that is 37516 is an example of this.  There are multiple buy values in Trans Code Description and the ID No. of the buy values are all different so I would like to create a new column that adds together all of the values in my Amount column that meet this criteria.  Not sure if a formula will work or if there is a tool that might be helpful.  Any ideas or thoughts would be much appreciated!!

[danilang]

hi @JMM2022 

Here's one way you can do it.  

Filter for the Buys and then summarize using count([ID]) and count distinct([ID}.  The next filter finds the ones where count = count distinct the join extracts these row and the final summarize totals them.

You're problem statement raises more questions though

-what if a Pair Off has differing IDs, but some of them repeat? 

-how do you figure out the Grand total for all the other Pair Off types?

Dan

[danilang]

Duplicate because the in-line reply threw an error but posted the reply anyway