Formula assistance.

Seatora
Seatora ✭✭✭✭
edited 01/10/24 in Formulas and Functions

I have this formula that works as it is:

=COUNTIFS([Priority Type/Level]:[Priority Type/Level], "1_Tier 1: Priority Project", [Stakeholder Department(s)]:[Stakeholder Department(s)], FIND("Legal", @cell) <> 0)

but instead of having it limited to only "1_Tier 1: Priority Project" projetcs, I need ot expand it to include all values listed below that are present in the "Priority Type/Level" column:

"1_Tier 1: Priority Project"

"2_Tier 2: Priority Project"

"3_Tier 3: Unprioritized"

"6_New Project Request"

"7_Parked"

"8_Completed"

"9_Cancelled"

Any assistance would be greatly appreciated!


Best Answer

  • Zachary Hall
    Zachary Hall ✭✭✭✭✭✭
    Answer ✓

    A helper column would be the easiest way to solve for this. Here are the formulas for that.

    Helper:

    • =IF(OR([Priority Type/Level]@row="1_Tier1: Priority Project",[Priority Type/Level]@row="2_Tier 2: Priority Project",[Priority Type/Level]@row="3_Tier 3: Unprioritized",[Priority Type/Level]@row="6_New Project Request",[Priority Type/Level]@row="7_Parked",[Priority Type/Level]@row="8_Completed",[Priority Type/Level]@row="9_Cancelled"),1,0)

    Project Count:

    • =SUMIFS([Helper]:[Helper],[Stakeholder Department(s)]:[Stakeholder Department(s)], FIND("Legal", @cell) <> 0)


    If you don't want to create a helper column, you could also just add the COUNTIFS() of each condition. Granted, the formula is long, but this would do it:

    • =COUNTIFS([Priority Type/Level]:[Priority Type/Level], "1_Tier 1: Priority Project", [Stakeholder Department(s)]:[Stakeholder Department(s)], FIND("Legal", @cell) <> 0)+COUNTIFS([Priority Type/Level]:[Priority Type/Level], "2_Tier 2: Priority Project", [Stakeholder Department(s)]:[Stakeholder Department(s)], FIND("Legal", @cell) <> 0)+COUNTIFS([Priority Type/Level]:[Priority Type/Level], "3_Tier 3: Unprioritized", [Stakeholder Department(s)]:[Stakeholder Department(s)], FIND("Legal", @cell) <> 0)+COUNTIFS([Priority Type/Level]:[Priority Type/Level], "6_New Project Request", [Stakeholder Department(s)]:[Stakeholder Department(s)], FIND("Legal", @cell) <> 0)+COUNTIFS([Priority Type/Level]:[Priority Type/Level], "7_Parked", [Stakeholder Department(s)]:[Stakeholder Department(s)], FIND("Legal", @cell) <> 0)+COUNTIFS([Priority Type/Level]:[Priority Type/Level], "8_Completed", [Stakeholder Department(s)]:[Stakeholder Department(s)], FIND("Legal", @cell) <> 0)+COUNTIFS([Priority Type/Level]:[Priority Type/Level], "9_Cancelled", [Stakeholder Department(s)]:[Stakeholder Department(s)], FIND("Legal", @cell) <> 0)

    Hope this helps!

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

Answers

  • Zachary Hall
    Zachary Hall ✭✭✭✭✭✭
    Answer ✓

    A helper column would be the easiest way to solve for this. Here are the formulas for that.

    Helper:

    • =IF(OR([Priority Type/Level]@row="1_Tier1: Priority Project",[Priority Type/Level]@row="2_Tier 2: Priority Project",[Priority Type/Level]@row="3_Tier 3: Unprioritized",[Priority Type/Level]@row="6_New Project Request",[Priority Type/Level]@row="7_Parked",[Priority Type/Level]@row="8_Completed",[Priority Type/Level]@row="9_Cancelled"),1,0)

    Project Count:

    • =SUMIFS([Helper]:[Helper],[Stakeholder Department(s)]:[Stakeholder Department(s)], FIND("Legal", @cell) <> 0)


    If you don't want to create a helper column, you could also just add the COUNTIFS() of each condition. Granted, the formula is long, but this would do it:

    • =COUNTIFS([Priority Type/Level]:[Priority Type/Level], "1_Tier 1: Priority Project", [Stakeholder Department(s)]:[Stakeholder Department(s)], FIND("Legal", @cell) <> 0)+COUNTIFS([Priority Type/Level]:[Priority Type/Level], "2_Tier 2: Priority Project", [Stakeholder Department(s)]:[Stakeholder Department(s)], FIND("Legal", @cell) <> 0)+COUNTIFS([Priority Type/Level]:[Priority Type/Level], "3_Tier 3: Unprioritized", [Stakeholder Department(s)]:[Stakeholder Department(s)], FIND("Legal", @cell) <> 0)+COUNTIFS([Priority Type/Level]:[Priority Type/Level], "6_New Project Request", [Stakeholder Department(s)]:[Stakeholder Department(s)], FIND("Legal", @cell) <> 0)+COUNTIFS([Priority Type/Level]:[Priority Type/Level], "7_Parked", [Stakeholder Department(s)]:[Stakeholder Department(s)], FIND("Legal", @cell) <> 0)+COUNTIFS([Priority Type/Level]:[Priority Type/Level], "8_Completed", [Stakeholder Department(s)]:[Stakeholder Department(s)], FIND("Legal", @cell) <> 0)+COUNTIFS([Priority Type/Level]:[Priority Type/Level], "9_Cancelled", [Stakeholder Department(s)]:[Stakeholder Department(s)], FIND("Legal", @cell) <> 0)

    Hope this helps!

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

  • Seatora
    Seatora ✭✭✭✭

    @Zach Hall - Thank you so much! I went with the longer formula option this time, and it worked perfectly!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!