Help combining multiple if statements with countif from another sheet to get a symbol

malbers

I have so many columns! Is there a way to merge some of these formulas and get what I'm looking for in one or two columns?

I want the system to look at the hours identified in the Week 1: Oct 1-7, 2023 column and then tell me if the reviewer is at capacity (with a symbol) based on the assignments assigned in another sheet.

Thanks!!

Week 1 Availability Formula: =IF([Week 1: Oct 1-7, 2023]@row = "0 Hours/Week", 0, IF([Week 1: Oct 1-7, 2023]@row = "5 Hours/Week", 1, IF([Week 1: Oct 1-7, 2023]@row = "10 Hours/Week", 2, IF([Week 1: Oct 1-7, 2023]@row = "15 Hours/Week", 3, IF([Week 1: Oct 1-7, 2023]@row = "20 Hours/Week", 4)))))

Week 1 Assignments Formula: =COUNTIF({ID}, ID@row)

Week 1 Capacity Formula: =SUM([Week 1 Availability]@row - [Week 1 Assignments]@row)

Symbol: =IF([Week 1 Capacity]@row = "0", "Empty", IF([Week 1 Capacity]@row = "1", "Quarter", IF([Week 1 Capacity]@row = "2", "Half", IF([Week 1 Capacity]@row = "3", "Three Quarter", IF([Week 1 Capacity]@row = "4", "Full")))))

Eric Law

@malbers What are you trying to keep? The easiest way to do this is to change your Week 1: Oct 1-7, 2023 to just be an integer. You can use a column description that says it's Hours/Week or label it in there. But if it is an integer, you won't need all the IF statements to convert it to an integer, your Week 1 availability column.

Once that is done, you just need to do is =IF((([Week 1: Oct 1-7, 2023]@row/5)-(COUNTIF({ID}, ID@row))) = "0", "Empty", IF((([Week 1: Oct 1-7, 2023]@row/5)-(COUNTIF({ID}, ID@row))) = "1", "Quarter", IF((([Week 1: Oct 1-7, 2023]@row/5)-(COUNTIF({ID}, ID@row))) = "2", "Half", IF((([Week 1: Oct 1-7, 2023]@row/5)-(COUNTIF({ID}, ID@row))) = "3", "Three Quarter", IF((([Week 1: Oct 1-7, 2023]@row/5)-(COUNTIF({ID}, ID@row))) = "4", "Full")))))

malbers

Forgot to add this!

5 hours = 1 assignment

10 hours = 2 assignments

15 hours = 3 assignments

20 hours = 4 assignments

Eric Law

@malbers What are you trying to keep? The easiest way to do this is to change your Week 1: Oct 1-7, 2023 to just be an integer. You can use a column description that says it's Hours/Week or label it in there. But if it is an integer, you won't need all the IF statements to convert it to an integer, your Week 1 availability column.

Once that is done, you just need to do is =IF((([Week 1: Oct 1-7, 2023]@row/5)-(COUNTIF({ID}, ID@row))) = "0", "Empty", IF((([Week 1: Oct 1-7, 2023]@row/5)-(COUNTIF({ID}, ID@row))) = "1", "Quarter", IF((([Week 1: Oct 1-7, 2023]@row/5)-(COUNTIF({ID}, ID@row))) = "2", "Half", IF((([Week 1: Oct 1-7, 2023]@row/5)-(COUNTIF({ID}, ID@row))) = "3", "Three Quarter", IF((([Week 1: Oct 1-7, 2023]@row/5)-(COUNTIF({ID}, ID@row))) = "4", "Full")))))

malbers

Thank you @Eric Law this helped me cut out a couple of columns and now is working beautifully! Appreciate your time (and expertise!)