I need help on this multiple condition calculation

[azgaman]

I need help on this:

I am trying to calculate the arrears for the following:



Whilst cost is fixed, I want to be able to get arrears only when Due is ticked(yes).


As in the case of the image, only Jan, Feb and Mar arrears must be calculated because Due is ticked.
Thank you.

[rpeare]

iif([DueJan] = -1, [Jan], 0) + iif([DueFeb] = -1, [Feb], 0) + iif([DueMar] = -1, [Mar], 0) + ... etc until you have all months accounted for.

[azgaman]

Thank you, rpeare, for the reply. I learnt that iif functions are nested up to 7 times. Could there be another way for 12 months?
All the same, as there is no harm in trying I'll get my hands dirty with this one. Thanks again.

[azgaman]

rpeare, upon second look at the formula it is not nested. Each iif is standing on its own. It should work. Thank you very much. I'll get back after using it.

[ssanfu]

Here is an alternative to rpeare's formula. The end result is the same, just without the IIF() function...

(Abs([DueJan]) * [Jan]) + (Abs([DueFeb]) * [Feb]) + (Abs([DueMar]) * [Mar]) + ... + (Abs([DueDec]) * [Dec])

[rpeare]

P.S. nested iifs aren't limited to 7, I've had more than that before. It really depends on the length of the statement as to how many nested iifs you can have (I believe I've had up to around 15 before but had to make the field names very short to handle it)

[azgaman]

Thank you for the help. The IIF function worked good. I had to make slight changes to suit my need. I will also try the abs function to see how it also works. I appreciate your help. Thanks once more.