Help me automate this excel formula using Alteryx & explain how it is working if possible

[kunalkhanna1132]

The formula is B3==SUMIFS('Fac'!AE:AE, 'Fac'!E:E, 1, 'Fac'!AE:AE, NUMBERVALUE(AG3)). This formula is for a column and thus AG row number changes as B column row number changes.

Explanation
AG3: This is a relative reference to the AG cell in the same row as the formula in the B column. As you drag the formula down, AG3 will change to AG4, AG5, etc., automatically.
SUMIFS: This function will sum the values in the AE column of the Fac sheet where the E column is 1 and the AE column matches the numeric value of the corresponding AG cell.

Would really appreciate some help in this. Thanks!

Please find the sample data down below!

[KGT · Accepted solution]

B3==SUMIFS('Fac'!AE:AE, 'Fac'!E:E, 1, 'Fac'!AE:AE, NUMBERVALUE(AG3))

This checks 2 conditions (Pink and Purple), and any condition where that matches, it sums column AE.

So, a filter with the following condition:

[E]=1 AND [AE]=[AG]

Followed by a summarise to sum column [AE]

Looking at the data, as it's on 2 sheets, you can use a join and so this is not too hard, but definitely a deviation from Excel methods. Take a look at the attached. Realistically, I would remove a couple of those tools (Select/Sort/RecordID/Green Container), but have put them in to help explain.

[kunalkhanna1132]

Thanks @KGT ! Really appreciate the help.

[Qiu]

@KGT 
I also made a sample flow and was suprising similar with yours. 😁