If/Then formula
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!
Best Answer

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.
If 2023 is your baseline:
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!
Answers

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?

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.

Hi @Kelly Cepicky,
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! 🙂

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.

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?

actually your formula worked!!!!
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?

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.
If 2023 is your baseline:
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!

Thank you!!!!

No problem, glad to have helped. 🙂
Help Article Resources
Categories
Check out the Formula Handbook template!