SUMIF of positive values only

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

Post_SUMIF_BonusCalc.xlsx

Workflow

Help

Accepted answers

Ben_H

Hi @AlexSTeryx,

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

This now looks to match your desired output exactly.

Regards,

Ben

Sumifs v2.yxzp

All comments

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

Sumifs.yxzp

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

Post_SUMIF_BonusCalc.xlsx

AlexSTeryx

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

Cheers

Alex

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703