Formula Error

Luke Serrato
Luke Serrato ✭✭✭
in Formulas and Functions

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!

Capture.PNG


· Share on FacebookShare on Twitter

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

    · Share on FacebookShare on Twitter

Answers

  • Nick Burrus
    Nick Burrus ✭✭✭✭✭✭

    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.

    · Share on FacebookShare on Twitter
  • Danielle Roach
    Danielle Roach ✭✭✭✭

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

    · Share on FacebookShare on Twitter
  • Luke Serrato
    Luke Serrato ✭✭✭

    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.

    · Share on FacebookShare on Twitter
  • Nick Burrus
    Nick Burrus ✭✭✭✭✭✭
    https://community.smartsheet.com/discussion/comment/264525#Comment_264525

    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.

    · Share on FacebookShare on Twitter
  • Catherine Shea
    Catherine Shea ✭✭✭
    image.png

    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"

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee

    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

    · Share on FacebookShare on Twitter
  • Catherine Shea
    Catherine Shea ✭✭✭
    https://community.smartsheet.com/discussion/comment/264634#Comment_264634


    · Share on FacebookShare on Twitter
  • Catherine Shea
    Catherine Shea ✭✭✭

    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)

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!