Formula Error

Options

Hey all,


I'm having trouble with a formula and either my brain is fried or I'm missing something (or both), because I've been staring at it for 45 minutes and can't figure out what's wrong with it.


I'm trying to get a RYGG status column to show: "" or Blank, if no date is entered in Next Deliverable Column, Grey if Disposition Column is "Complete", Red if Next Deliverable Column is greater than Today, Yellow if Next Deliverable Column is within 14 days of Today, and Green, if none of the other criteria are met.


Any help would be appreciated!


Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Catherine Shea

    No problem at all. To do this, just add in the extra column range ([GRE/GSC Responsible] column), then the criteria for that column, 1. (1 means that the flag is raised or true.)

    Try this:

    =COUNTIFS(Tasks:Tasks, CONTAINS("Map", @cell), [Status Update]:[Status Update], "In Progress", [GRE/GSC Responsible]:[GRE/GSC Responsible], 1)


    Let me know if this works for you!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Nick Burrus
    Nick Burrus ✭✭✭✭✭✭
    Options

    It is typically best practice to use double quotes like " not '. In addition (TODAY() +14) isn't exactly valid it should be TODAY(14).


    If you can share your sheet with me at NBurrus@Stria.com with editor or admin access I'll be happy to look into your formula asap.

    Dr. St Nicholas Burrus DHA, PMP

    I build Smartsheets for the US Government, State Government, and about a dozen of the US Fortune 100s.

  • Danielle Roach
    Danielle Roach ✭✭✭✭
    Options

    Try thisThis is a formula I use and it works great - try it this way

    =IF([% Complete]1 = "1", "Gray", IF(Finish1 = "", "Yellow", IF(Finish1 < TODAY(), "Red", "Green")))

  • Luke Serrato
    Options

    NBurrus, Invite sent to you.


    I'm not sure if my text is not coming through correctly, but I am using double quotes " and ". I am also finding that TODAY(14) and TODAY()+14 are both working for me.


    Danielle, I can't see how that formula would accomplish what I'm trying to... I'm trying to include a blank, so my status light column remains blank if no date has been entered.

  • Nick Burrus
    Nick Burrus ✭✭✭✭✭✭
    Options

    Great use of column formulas. Check out row eighteen under Status Formula for a new formula:


    =IF(Disposition@row = "Complete", "Gray", IF([Next Deliverable]@row = "", "", IF([Next Deliverable]@row < TODAY(), "Red", IF([Next Deliverable]@row < (TODAY() + 14), "Yellow", "Green"))))


    I added a blank that supercedes the others. :) Use this formula in your columns and it'd do the trick

    Dr. St Nicholas Burrus DHA, PMP

    I build Smartsheets for the US Government, State Government, and about a dozen of the US Fortune 100s.

  • Catherine Shea
    Options

    I am a novice to formulas and not sure how to approach this. The Rows may be deleted or added so If I can use the column names to calculate I would prefer to put it on a sheet summary.

    I am looking for several counts.

    I need a count of how many observations I have in total that are low and or high. Along with the status updates on them (my drop down options are "In Progress", "Delayed" or "Closed"

    I also need to know in my task column how many MAP I have that are flagged to be true that are "In Progress", "Delayed", or "Closed"

  • Genevieve P.
    Options

    Hi @Catherine Shea

    Happy to help with this!

    You can use COUNTIFS to count rows that meet very specific criteria in certain columns (see here for info on this), and yes, you can use full column references and put these formulas in your Sheet Summary fields.

    A COUNTIFS works like this:

    =COUNTIFS([Column1]:[Column1], "Criteria", [Column2]:[Column2], "Criteria 2") etc.

    For your formula, I'd use the CONTAINS function to check and see if that Task column contains the word "observation". Here's an example of how to look for a "Low" priority. To change this to look for "high", just update the text at the very end of the formula:

    =COUNTIFS(Tasks:Tasks, CONTAINS("Observation", @cell), Priority:Priority, "Low")


    Now, to find specific Status Updates within this criteria, all you have to do is add in the other column as a new range and specify a new criteria.

    =COUNTIFS(Tasks:Tasks, CONTAINS("Observation", @cell), Priority:Priority, "Low", [Status Update]:[Status Update], "In Progress")

    Change what you're looking for after that Status Update "in quotes" to create your other calculations.

    Does this make sense how to create a COUNTIFS statement? If you need more help with your final one, let me know and I'm happy to clarify what's needed.

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Catherine Shea
    Options

    HI Genevieve,

    Thanks for the assistance. I do need some help with the last one. I want to to know how many Flags that are TRUE for the GRE/GSC Responsible that contain a "Map" in the column of tasks are "In Progress'" (Delayed and Closed)

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Catherine Shea

    No problem at all. To do this, just add in the extra column range ([GRE/GSC Responsible] column), then the criteria for that column, 1. (1 means that the flag is raised or true.)

    Try this:

    =COUNTIFS(Tasks:Tasks, CONTAINS("Map", @cell), [Status Update]:[Status Update], "In Progress", [GRE/GSC Responsible]:[GRE/GSC Responsible], 1)


    Let me know if this works for you!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!