Formula Question-I need to create a IF formula based on other cells criteria
I need this formula in the June 2024 column and below is what I need it to do. It should be one big long formula and I'm assuming it would be a IF statement. I could set up automations for this but that's a lot more work than just figuring out a formula since I am going to use this formula for many months to come on this sheet.
If capacity group is 1, June duration <90 = 0
If capacity group is 1, June duration 91-180 = 37.5
If capacity group is 1, June duration >181 = 75
If capacity group is 2, June duration <90 = 0
If capacity group is 2, June duration 91-180 = 37.5
If capacity group is 2, June duration 181-300 = 60
If capacity group is 2, June duration >300 = 75
Thank you!
Best Answer
-
=IF(And([Capacity Group]@row = 1, [June Duration]@row <= 90),0,IF(And([Capacity Group]@row = 1, [June Duration]@row > 90, [June Duration]@row <=180), 37.5, IF(And([Capacity Group]@row = 1, [June Duration]@row >180),75,IF(And([Capacity Group]@row = 2, [June Duration]@row <= 90),0,IF(And([Capacity Group]@row = 2, [June Duration]@row > 90, [June Duration]@row <=180),37.5,IF(And([Capacity Group]@row = 2, [June Duration]@row >180,[June Duration]@row<=300),60,IF(And([Capacity Group]@row = 2, [June Duration]@row >300),75)))))))
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Answers
-
=IF(And([Capacity Group]@row = 1, [June Duration]@row <= 90),0,IF(And([Capacity Group]@row = 1, [June Duration]@row > 90, [June Duration]@row <=180), 37.5, IF(And([Capacity Group]@row = 1, [June Duration]@row >180),75,IF(And([Capacity Group]@row = 2, [June Duration]@row <= 90),0,IF(And([Capacity Group]@row = 2, [June Duration]@row > 90, [June Duration]@row <=180),37.5,IF(And([Capacity Group]@row = 2, [June Duration]@row >180,[June Duration]@row<=300),60,IF(And([Capacity Group]@row = 2, [June Duration]@row >300),75)))))))
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
@Amy Merrill As I was considering your question, have you thought about having a separate Capacity Cross Reference Smartsheet holding the Capacity Matrix with the fields below? Its more flexible and scalable.
1) Created By
2) Created Date
3) Index (Auto #)
3) Capacity Group
4) Minimum Endpoint
6) Maximum Endpoint
7) Result Value
8) Start Date
9) End Date
10) Comments
11) Modified By
12) Modified Date
In your Smartsheet you can then utilize the SUMIFS as a multi criteria VLOOKUP.
=SUMIFS({Capacity Cross Reference Get Result Value}, {Capacity Cross Reference Get Capacity Group}, [Capacity Group]@row, {Capacity Cross Reference Get Minimum Endpoint}, <=[June Duration]@row, {Capacity Cross Reference Get Maximum Endpoint}, >=[June Duration]@row)
Sometimes Capacity fluctuates with seasonality and economic impact. If your Smartsheet has a Created By and Created Date, then you can pull the Created Date into the SUMIF formula where the Capacity Cross Reference Smartsheet can expand to the below.
=SUMIFS({Capacity Cross Reference Result Value}, {Capacity Cross Reference Capacity Group}, [Capacity Group]@row, {Capacity Cross Reference Minimum Endpoint}, <=[June Duration]@row, {Capacity Cross Reference Maximum Endpoint}, >=[June Duration]@row, {Capacity Cross Reference Start Date}, <=Created Date]@row, {Capacity Cross Reference End Date}, >=[Created Date]@row)Erin Horiuchi Green, MBA, LSSYB, PSMI
Process Manager
Syneos Health
Please kindly like ❤️, upvote ⬆️ and/or mark ✅ any of my contributions that have provided value.
Core App and Project Managment Certified 🚀 -
@Amy Merrill As a bonus, you can make the June 2024 formula a Column Formula and replicate for subsequent months.
Erin Horiuchi Green, MBA, LSSYB, PSMI
Process Manager
Syneos Health
Please kindly like ❤️, upvote ⬆️ and/or mark ✅ any of my contributions that have provided value.
Core App and Project Managment Certified 🚀 -
Thank you for the responses! The first formula works and Erin's has me thinking about different ways to get the same result. Thank you for expanding my understanding and knowledge, it's greatly appreciated.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!