Formula for Priority Cell
Hello All:
I am having a difficult time creating a formula that will give me Priority. Below are my rules for Priority Status. Thanks
Urgency High
If Impact High and Urgency High, then Priority is 1
If Impact Med and Urgency High, then Priority 2
If Impact Low and Urgency High, then Priority 3
Urgency Med
If Impact High and Urgency is Med, then Priority 2
If Impact Med and Urgency is Med, then Priority 3
If Impact Low and Urgency is Med, then Priority 4
Urgency Low
If Impact High and Urgency is Low, then Priority 3
If Impact Med and Urgency is Low, then Priority 4
If Impact Low and Urgency is Low, then Priority 5
Comments

Here is the first part of my formula for Urgency High and Impact High:
=IF(AND(Impact12 = "High", Urgency12, "High"), "1"))

Hi,
Try something like this.
=IF(AND(Impact@row = "High"; Urgency@row = "High"); 1; IF(AND(Impact@row = "Med"; Urgency@row = "High"); 2; IF(AND(Impact@row = "Low"; Urgency@row = "High"); 3; IF(AND(Impact@row = "High"; Urgency@row = "Med"); 2; IF(AND(Impact@row = "Med"; Urgency@row = "Med"); 3; IF(AND(Impact@row = "Low"; Urgency@row = "Med"); 4; IF(AND(Impact@row = "High"; Urgency@row = "Low"); 3; IF(AND(Impact@row = "Med"; Urgency@row = "Low"); 4; IF(AND(Impact@row = "Low"; Urgency@row = "Low"); 5)))))))))
The same version but with the below changes for your and others convenience.
=IF(AND(Impact@row = "High", Urgency@row = "High"), 1, IF(AND(Impact@row = "Med", Urgency@row = "High"), 2, IF(AND(Impact@row = "Low", Urgency@row = "High"), 3, IF(AND(Impact@row = "High", Urgency@row = "Med"), 2, IF(AND(Impact@row = "Med", Urgency@row = "Med"), 3, IF(AND(Impact@row = "Low", Urgency@row = "Med"), 4, IF(AND(Impact@row = "High", Urgency@row = "Low"), 3, IF(AND(Impact@row = "Med", Urgency@row = "Low"), 4, IF(AND(Impact@row = "Low", Urgency@row = "Low"), 5)))))))))
Depending on your country you’ll need to exchange the comma to a period and the semicolon to a comma.
Did it work?
Hope that helps!
Have a fantastic day!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Depending on your setup, you could also use a "Priority Table" of sorts. Take a look at the screenshot below. We are basically using an INDEX function to look at the table, using a MATCH function to determine the row number off of the Impact, and another MATCH function to determine the column number off of the Urgency.
The formula used in the screenshot is as follows:
=INDEX($[High Urgency]$2:$[Low Urgency]$4, MATCH($Impact@row, $ImpactTable$2:$ImpactTable$4, 0), MATCH($Urgency@row, $[High Urgency]$1:$[Low Urgency]$1, 0))
.
All you would need to do is update column names and row references based on your actual column names and the actual row numbers used.

Excellent! It worked perfectly. Thanks Andree

Happy to help!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

I am using this same formula approach for a different cell but receiving an argument error. Not sure what I am doing wrong. Can you Help?
=COUNTIFS(AND(Priority@row = "P1", Status@row = "Open"))

Happy to help!
You don't need the AND function in the formula.
Did it work?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

No, incorrect argument
=COUNTIF(Priority@row, ="P1", Status@row = "Open")

Change to COUNTIFS.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.


Ok, that worked but for some reason my results are 0. However when I look at the Priority and Status Column, I have several P1 that meet the criteria.
Does Sub Task impact the results?

You are using specific cell references in your formula that are pointing to the cells on the same rows as the formula.
Looking across the same row, you will never have a count higher than 1.
To look at the entire columns for your ranges, you will need to specify that in your formula.
[Column Name]:[Column Name]
Repeating the column name with a separator of a colon and no row references with tell the formula to look at the entire column.

Thanks everyone who contributed to this thread... Below is what I'm using the populate priority.
Sharing because my formula was coming up as #UNPARSEABLE and I couldn't figure out why.
Turns out that I needed to also include quotes around my return values since they also contained text, and not just numbers likes the examples above.
Hope this helps someone :)
Good Luck!
=IF(AND(Impact@row = "0  Critical", Urgency@row = "0  Emergency"), "0  Emergency",
IF(AND(Impact@row = "0  Critical", Urgency@row = "1  Critical"), "1  High",
IF(AND(Impact@row = "0  Critical", Urgency@row = "2  Significant"), "1  High",
IF(AND(Impact@row = "0  Critical", Urgency@row = "3  Minor"), "2  Medium",
IF(AND(Impact@row = "1  Major", Urgency@row = "0  Emergency"), "1  High",
IF(AND(Impact@row = "1  Major", Urgency@row = "1  Critical"), "1  High",
IF(AND(Impact@row = "1  Major", Urgency@row = "2  Significant"), "2  Medium",
IF(AND(Impact@row = "1  Major", Urgency@row = "3  Minor"), "3  Low",
IF(AND(Impact@row = "2  Significant", Urgency@row = "0  Emergency"), "1  High",
IF(AND(Impact@row = "2  Significant", Urgency@row = "1  Critical"), "2  Medium",
IF(AND(Impact@row = "2  Significant", Urgency@row = "2  Significant"), "3  Low",
IF(AND(Impact@row = "2  Significant", Urgency@row = "3  Minor"), "3  Low",
IF(AND(Impact@row = "3  Minor", Urgency@row = "0  Emergency"), "2  Medium",
IF(AND(Impact@row = "3  Minor", Urgency@row = "1  Critical"), "3  Low",
IF(AND(Impact@row = "3  Minor", Urgency@row = "2  Significant"), "3  Low",
IF(AND(Impact@row = "3  Minor", Urgency@row = "Low"), "3  Low")))))))))))))))
Help Article Resources
Categories
Check out the Formula Handbook template!