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

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

Find more posts tagged with

Formulas

Accepted answers

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

All comments

malbers

Forgot to add this!

5 hours = 1 assignment

10 hours = 2 assignments

15 hours = 3 assignments

20 hours = 4 assignments

Eric Law

malbers

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.