Need to build a formula that grows each month

Options

Good Afternoon All,

I have a formula that has to build for each month

I already have the formula for January and it works

IF({January A Sum} <> 0, {January A Sum}, {January F Sum})

The February formula is bigger

IF Jan A Sum and Feb A sum <> 0, it would equal Jan A Sum + Feb A sum

If Jan A sum <> 0 and Feb A sum = 0, Jan A Sum + Feb F sum

Else it is Jan F Sum + Feb F Sum

When you get to March you have to add more

If Jan A Sum and Feb A Sum and March A Sum <> 0, Jan A Sum + Feb A Sum + March A Sum

If Jan A sum and Feb A sum <>0 and March A Sum = 0, Jan A Sum + Feb A Sum + March F Sum

If Jan A sum <>0 and Feb A Sum and March A Sum = 0, Jan A Sum + Feb F Sum + March F Sum

Else Jan F Sum + Feb F Sum + March F Sum

This equation will grow each month if I cannot find a better way to handle this. Anyone have any help to offer?

Just for Reference the sums are referenced on another sheet and are not side by side to one another on the sheet.

Caroline

Answers

  • AravindGP
    AravindGP ✭✭✭✭✭✭
    Options

    Hi Caroline,


    If you do not want a lengthy formula, you can create helper columns in the source sheet with different scenarios and even do up the totals. You can then pull the absolute value into your new sheet.

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

  • delaurellc
    delaurellc ✭✭✭
    Options

    Don't want to add a bunch more columns just to get a number, I will keep working on it and see what else I can come up with.,

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!