SUMIF of positive values only

[AlexSTeryx]

Hi Alteryx Community!
I have an issue with our rather complex bonus calculation.

Based on target achievement in % I receive a value for bonus entitlement per month (columns C to H), Based on that I calculate the bonus entitlement YTD/Year To Date (for January = column C * 1, for Feburary = column D * 2, etc.). 
And now comes the challenge: 

In the next step I balance the bonus entitlement YTD of the current month with the bonus entitlement YTD of the previous months. To do so I apply the formula as shown in the screen shot ("SUMMEWENN" = SUMIF). In detail the formula is in column O "=I7, in column P "=J7-SUMIF($O7:O7;">0")", in column Q "=K7-SUMIF($O7:P7;">0")" etc.
So the question is: how to 
a) summarise only the positive values and

b) how to expand the number of columns summarised  

In the attached file are two sheets: "Columns" where the data is as presented above, and "Rows" on which I swooped the columns, in case that easier to use in Alteryx (the latter only with values, formulas you'll only find on the sheet "Columns"). 

In case you need more information, please do not hesitated asking for it.
Thanks for your help and support

Alex

[Ben_H]

Hi @AlexSTeryx,

Try the attached,

I used the second tab, it's much easier to work in that orientation.

This setup should work regardless of how many months you have in there.

It just uses a few formulas and a multi-row.

Regards,

Ben

[AlexSTeryx]

Thank you @Ben_H !

That looks already quite good. I also played around with the Multi-Row-Formula-Tool but seemed to miss the final tweak.

However, the tricky part is the next step: especially when the balance turns negative. In this case the negative value has to be ignored. So for employee B the Pay Out in March is 160 rather than 180.

Do you have an idea, how to solve that one?

I added two more employees with a more volatile target achievement to better illustrate it.

As long as the performance is towards the end better then at the beginning or in-between the sum of the payouts equals the bonus entitlement of June. In case the target achievement drops towards June, the sum of the payouts may exceed the final bonus entitlement (Employee C).

Best

Alex

[Ben_H · Accepted solution]

Hi @AlexSTeryx,

Yes my mistake! I've updated the multirow formula slightly.

This now looks to match your desired output exactly.

Regards,

Ben

[AlexSTeryx]

hi @Ben_H!
That looks promising! Thanks a lot! I'll check it tomorrow with a bit more time, and accept and like it, if it works.
Have a nice day

Alex

[AlexSTeryx]

Hi @Ben_H !
Sorry I was occupied with other things the last days.
You solution is perfect. Thanks a lot.

Cheers

Alex