Formula Error

Luke Serrato

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!

Genevieve P.

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

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.

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

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.

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

Catherine Shea

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.

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

Catherine Shea

https://community.smartsheet.com/discussion/comment/264634#Comment_264634

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)

Genevieve P.

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