# Formula for Priority Cell

Options
edited 12/09/19

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:

• Options

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

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.

• Options

Excellent! It worked perfectly. Thanks Andree

• ✭✭✭✭✭✭
Options

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.

• Options

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

• ✭✭✭✭✭✭
Options

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.

• Options

No, incorrect argument

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

and add a comma after Status@row.

• Options

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?

• ✭✭✭✭✭✭
Options

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.

• Options

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!