Writing a Nested if with a sum if statement in Alteryx

Question

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 Answer

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.

estherb47 · Accepted Answer

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