Formula QuestionI 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 91180 = 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 91180 = 37.5
If capacity group is 2, June duration 181300 = 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) 
@Amy Merrill As a bonus, you can make the June 2024 formula a Column Formula and replicate for subsequent months.

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
Check out the Formula Handbook template!