Formula Question-I need to create a IF formula based on other cells criteria

Amy Merrill

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!

Mark.poole

@Amy Merrill

=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)))))))

Mark.poole

@Amy Merrill

=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)))))))

Erin Horiuchi Green

@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

@Amy Merrill As a bonus, you can make the June 2024 formula a Column Formula and replicate for subsequent months.

Amy Merrill

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.