Replicating a SUMIFS formula from Excel in Alteryx

[MatthiasK121]

Hello Alteryx Community,

I am currently working with a database in Alteryx and came across one function that I could not find. I hope someone can help me out here.

The data structure is similar to below and I want to calculate the Year-To-Date value which should return the values in the "red" column. The excel formula for this would be:   =SUMIFS($C$8:$C$31;$A$8:$A$31;A8;$B$8:$B$31;"<="&B8)

Name	Month ID	Value	Value YTD
A	202201	5	5
A	202202	5	10
A	202203	5	15
A	202204	5	20
A	202205	5	25
A	202206	5	30
A	202207	5	35
A	202208	5	40
A	202209	5	45
A	202210	5	50
A	202211	5	55
A	202212	5	60
B	202201	5	5
B	202202	5	10
B	202203	5	15
B	202204	5	20
B	202205	5	25
B	202206	5	30
B	202207	5	35
B	202208	5	40
B	202209	5	45
B	202210	5	50
B	202211	5	55
B	202212	5	60

Thank you! :) 

[binuacs]

@MatthiasK121 one way of doing this with the Running Total tool

[IraWatt]

Hey @MatthiasK121,

To achieve this you can use a multirow formula tool like this:

IF [Month ID]>[Row-1:Month ID] THEN [Row-1:Value YTD]+[Value] ELSE [Value] ENDIF

The community has some quick and easy videos on formulas and the here Writing Expressions If you want to learn more about the Multi-Row-Formula Tool the community has some quick and easy videos on the topic here: Multi-Row-Formula

My formula just checks the current month ID is greater then the previous. If true then add the rows values on else user the current value.

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com