Formula for Priority Cell

AB
AB
edited 12/09/19 in Formulas and Functions

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

Tags:

Comments

  • AB
    AB

    Here is the first part of my formula for Urgency High and Impact High:

    =IF(AND(Impact12 = "High", Urgency12, "High"), "1"))

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    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 semi-colon 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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.

    Comm.PNG

  • AB
    AB

    Excellent! It worked perfectly. Thanks Andree

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Happy to help!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • AB
    AB

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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • AB
    AB

    No, incorrect argument

     

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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Change to COUNTIFS.

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • AB
    AB

    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?

     

     

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!