Formula assistance.

Seatora

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!

Zachary Hall

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!

Zachary Hall

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!

Seatora

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