# If/Then formula

Options

I need to perform a calculation from 3 columns if criteria in a third column is met. What is the formula? Any help is appreciated!

• ✭✭✭✭✭✭
Options

Totally possible, though the formula might not necessarily be thought of as "quick"! This is also only taking months into account, so it's only looking at the month number and disregards the day.

Formulas are:

2023:

=IFERROR(IF(AND(YEAR(Start@row) = 2023, YEAR(Start@row) = YEAR(End@row)), MONTH(End@row) - MONTH(Start@row), IF(AND(YEAR(Start@row) < 2023, YEAR(End@row) = 2023), MONTH(End@row), IF(AND(YEAR(Start@row) < 2023, YEAR(End@row) >= 2023), 12, 0))), "")

2024:

=IFERROR(IF(AND(YEAR(Start@row) = 2024, YEAR(Start@row) = YEAR(End@row)), MONTH(End@row) - MONTH(Start@row), IF(AND(YEAR(Start@row) < 2024, YEAR(End@row) = 2024), MONTH(End@row), IF(AND(YEAR(Start@row) <= 2024, YEAR(End@row) >= 2024), 12, 0))), "")

2025:

=IFERROR(IF(AND(YEAR(Start@row) = 2025, YEAR(Start@row) = YEAR(End@row)), MONTH(End@row) - MONTH(Start@row), IF(AND(YEAR(Start@row) < 2025, YEAR(End@row) = 2025), MONTH(End@row), IF(AND(YEAR(Start@row) < 2025, YEAR(End@row) >= 2025), 12, 0))), "")

As you can see, if you were going further out then just change the year number to the relevant value.

This was a bit hurried, so may not be perfect for your purposes. Otherwise I'd probably recommend doing something with one of the functions similar to NETDAYS for the calculations. Hopefully it points you in the right direction though!

• ✭✭✭✭✭✭
Options

Hi @Kelly Cepicky - Do you have any other specific criteria you can share, like column names, column type, and what you need the calculation to do?

• Options

If column "FTE Savings" has a number (any number), then I need "Monthly Savings" multiplied by "# of Months in the year" to give total savings for that year. If "FTE Savings" is zero, then leave blank. Hope that helps.

• ✭✭✭✭✭✭
Options

This should do what you're after:

=IF([FTE Savings]@row <> "", ([Monthly Savings]@row * [# of Months in the year]@row), "")

If you have a number in FTE Savings, but one/both of the other 2 columns is missing data then your Total Savings will show 0.

Sample data:

Hope this helps, but if you've any problems/questions then just post! 🙂

• Options

That formula works but..................

I have another problem. The FTE Savings will have occurred in either 2023, 2024 or 2025. How do I factor that into the calculation? There is a start date & a completion date & those can both be in different years. I need to be able to calculate the savings based on each year & how much of that savings falls into each year. I hope that makes sense. This would be so much easier on a call, lol.

• ✭✭✭✭✭✭
Options

Does the amount of savings vary by year, or is it constant?

I am guessing you would like some additional columns for the breakdown of how much is saved per year?

• Options

Is there a simple way to calculate the number of months for each year that a row item has? For example, if in a row the item has a start date of 5/1/2023 & an end date of 4/30/2024, is there a quick formula that will calculate how many months are in each year?

• ✭✭✭✭✭✭
Options

Totally possible, though the formula might not necessarily be thought of as "quick"! This is also only taking months into account, so it's only looking at the month number and disregards the day.

Formulas are:

2023:

=IFERROR(IF(AND(YEAR(Start@row) = 2023, YEAR(Start@row) = YEAR(End@row)), MONTH(End@row) - MONTH(Start@row), IF(AND(YEAR(Start@row) < 2023, YEAR(End@row) = 2023), MONTH(End@row), IF(AND(YEAR(Start@row) < 2023, YEAR(End@row) >= 2023), 12, 0))), "")

2024:

=IFERROR(IF(AND(YEAR(Start@row) = 2024, YEAR(Start@row) = YEAR(End@row)), MONTH(End@row) - MONTH(Start@row), IF(AND(YEAR(Start@row) < 2024, YEAR(End@row) = 2024), MONTH(End@row), IF(AND(YEAR(Start@row) <= 2024, YEAR(End@row) >= 2024), 12, 0))), "")

2025:

=IFERROR(IF(AND(YEAR(Start@row) = 2025, YEAR(Start@row) = YEAR(End@row)), MONTH(End@row) - MONTH(Start@row), IF(AND(YEAR(Start@row) < 2025, YEAR(End@row) = 2025), MONTH(End@row), IF(AND(YEAR(Start@row) < 2025, YEAR(End@row) >= 2025), 12, 0))), "")

As you can see, if you were going further out then just change the year number to the relevant value.

This was a bit hurried, so may not be perfect for your purposes. Otherwise I'd probably recommend doing something with one of the functions similar to NETDAYS for the calculations. Hopefully it points you in the right direction though!

• Options

Thank you!!!!

• ✭✭✭✭✭✭
Options

No problem, glad to have helped. 🙂

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!