If Sumif from Second Input

Hi,

I am trying to recreate a nested IF SUMIF statement in Alteryx. The issue is, that the criteria and the range for the criteria to be found in and the sum range are found in multiple different sheets (and therefore, multiple inputs) and the statement I am working with is quite long and has multiple criteria and different sumifs for each criteria.

I thought that I could create my sumif column on sheet 2 by using a summarize tool, then join sheet 1 and 2, and finally use an if then statement in the formula tool to return the value from the sumif column if the criteria is met. However, I am unsure of the best way to join my two data sets together.

The specific statement I am trying to recreate is this:

"=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!AB:AB,G2,Repo!AC:AC)," & _
"IF(E2=""Repo Principal"",SUMIF(Repo!F:F,G2,Repo!P:P)," & _
"IF(E2=""Fees BBL"",SUMIF(Repo!AB:AB,G2,Repo!AC:AC)," & _
"IF(E2=""Cash Margin Principal"",-SUMIF(Funding!BJ:BJ,G2,Funding!P:P)," & _
"IF(E2=""Cash Margin Interest"",SUMIF(Funding!BK:BK,G2,Funding!AW:AW)," & _
"IF(E2=""Funding Principal"",-SUMIF(Funding!BJ:BJ,G2,Funding!P:P)," & _
"IF(E2=""Funding Interest"",SUMIF(Funding!BK:BK,G2,Funding!AW:AW),""NA""))))))))))")

Unfortuneatly, cannot share my data, but I have attached a small sample workflow to better show what I am trying to do.

Any help is appreciated! Thank You!!

Sample for Sumif.yxmd

Tips and Tricks

Connectors

Join

Accepted answers

danilang

Hi @grsomer

You already applied the summarizing concepts that @BrandonB mentioned in his post. You just need the Join and a little clean up.

The Multi-join tool joins Field3 from your input to Field16 and field12 from you summarized data tables resulting in this intermediate data

The data you need is now in either Sum_Field 17 or Sum_Field 4. The formula tool copies the non-null value to your Sumif column. After some clean up, you have this result

Dan

Sample for Sumif V2.yxmd

All comments

BrandonB

Generally you can avoid the need to use sumifs by using the summarize tools and grouping by the category at the level of granularity that you require.

If your data to be aggregated across isn't already in one value column, you may need a transpose tool checking the key column box for your E2 and leaving the boxes checked for all of the columns to be transposed into one. Then you can summarize by grouping on E2 and summing the values column.

As it relates to multiple data sets being brought together, a union tool can be leverage to stack data with the same column names. If the column names you are trying to stack are not the same, you can use a select tool to make them the same. If you need to link data together similar to a vlookup, you would instead want to use either the Find & Replace or the Join tool.

danilang

Hi @grsomer

You already applied the summarizing concepts that @BrandonB mentioned in his post. You just need the Join and a little clean up.

The Multi-join tool joins Field3 from your input to Field16 and field12 from you summarized data tables resulting in this intermediate data

The data you need is now in either Sum_Field 17 or Sum_Field 4. The formula tool copies the non-null value to your Sumif column. After some clean up, you have this result

Dan

Sample for Sumif V2.yxmd

grsomer

@danilang thank you!! This is exactly what I needed!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703