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]

Would it be possible to provide a small sample data set? Using a summarize tool saying group by E2 in this case and then sum the value column normally takes care of it. If there are values across multiple columns you can use a transpose tool first to get all of the values in one column and then summarize after. 

[grsomer]

Hi @BrandonB Thank you for your help! I believe you had helped me with a similar issue that I have posted in the community before - attached is the same sample workflow that I provided in that post. Hopefully this helps illustrate what I am doing. 

[BrandonB · Accepted solution]

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.