Conditional Sumif

grsomer

Hi, 

I have quite a complex issue that I need help solving in Alteryx. 

I am trying to create the function: 

"=IF(E2=""Bond Accrual"",SUMIF(Bonds!AA:AA,G2,Bonds!AD:AD)," & _
"IF(E2=""Bond MTM"",SUMIF(Bonds!E:E,G2,Bonds!O:O)," & _
"IF(E2=""Bonds Unsettled"",SUMIF(BondUnsettled!BT:BT,G2,BondUnsettled!BJ:BJ)," & _
"IF(E2=""Repo Interest"",SUMIF(Repo_DFI!AB:AB,G2,Repo_DFI!AC:AC)," & _
"IF(E2=""Repo Principal"",SUMIF(Repo_DFI!F:F,G2,Repo_DFI!P:P)," & _
"IF(E2=""Fees BBL"",SUMIF(Repo_DFI!AB:AB,G2,Repo_DFI!AC:AC)," & _
"IF(E2=""Cash Margin Principal"",SUMIF(Repo_DFI!F:F,G2,Repo_DFI!P:P)," & _
"IF(E2=""Cash Margin Interest"",SUMIF(Repo_DFI!AB:AB,G2,Repo_DFI!AC:AC)," & _
"IF(E2=""Funding Principal"",SUMIF(Repo_DFI!F:F,G2,Repo_DFI!P:P)," & _
"IF(E2=""Funding Interest"",SUMIF(Repo_DFI!AB:AB,G2,Repo_DFI!AC:AC),""NA""))))))))))")

It is basically a conditional sumif, saying that if column E contains a certain input, then enter a corresponding sumif in the column this formula is written in. The issue is that each sumif is coming from a different sheet, which is not already in the workflow. 

My current configuration is inputting all of the needed sheets, using a summarize tool to do the sumif on each input, then joining them all with a join multiple tool to the main stream. Finally, I use a formula tool to write an If then statement that replicates the if statements above. However, it seems as though the sumif is not being matched properly with its corresponding ID string after the formula. 

Is there another way to better replicate this formula? 

Any help is appreciated! Let me know if any additional information is needed (unfortunately I cannot share the entire workflow due to confidentiality). 

BrandonB

Thoughts on the attached solution? Summarizing and renaming the fields to the same column names (Name and Value in this case) prior to union so they are stacked on top of each other and then summarizing across all three data sets grouping by the Name column. I know that you are trying to relate this concept back to your actual dataset, so I think the easiest way to explain it is you need the column to group by in one column and the values to sum up all in one column, so when you use a summarize tool you can group by the name column and sum the value column. 

Example Sumif.yxmd