Writing a Nested if with a sum if statement in Alteryx

[DeanoNY]

Hi I am having issues trying to recreate this excel if statement in Alteryx  

=IF(AND(Run/Submit="Run",Product_App="MMA",Plan_Code=351),NonPR Units+SUMIF(Run/Submit,"Run",PR Units),IF(Run/Submit="Run",PR Units,0))

IF [Run/Submit]='Run' AND [PRODUCT_APP]='MMA' AND [PLAN_CODE]='351' then [Non PR Units]
ELSEIF [Run/Submit]='Run' then [PR Units]
ELSE '0'
ENDIF

I cant tie out to the number of total units with this formula in Alteryx, I keep getting malformed error when trying to add sumif 

[apathetichell · Accepted solution]

O.k. - first off are you putting this in a new field - and if so - is that field supposed to be string or integer? Currently you are using strings as returns ("0" is a string - 0 is an integer). so you may want to adjust it.

Next up - Alteryx doesn't do sumif. You can sum fields in a formula/multi-formula tool(ie [1]+[2]) would sum a field named [1] with one named [2] and most importantly - in summarize.

Basically summarize is sumif on steroids. You have to create a group (ie your if) and then you use group by (your if column) and sum your data column.

[DeanoNY]

Thank you very much for the help and advice. I am using the new column as a string it is the sumif that I could not figure out. I will give it a try using your advice. Once again thank you! 

[apathetichell]

O.k. - If you are stumbling on something upload part of the workflow where you are trying to replicate sum/ifs and I'm sure someone will be able to quickly help.

[estherb47 · Accepted solution]

Hi @DeanoNY 

You can use a filter for the conditions to meet when you use the sumif (e.g., Run/Submit="Run" etc), and then summarize on the PR Units for the result of the filter with a summarize tool. Connect that summarized data back to your filtered data with a paste append to grab just what's been added, and a formula tool to add to the Non PR Units field.

Cheers!

Esther