RYG Ball Formula Issue

Jeff Pindak
Jeff Pindak ✭✭✭
in Formulas and Functions

We have used:

=COUNTIF([Risk Tolerance]:[Risk Tolerance], "Green")

and with red and yellow respectively, to count the total balls in a column.

About a month ago it just stopped working. Tried re-entering a few ways, but it

keeps saying #INVALID OPERATION. I want to convert to a Column formula now that SS does that, but i cant get it working to do that.


ALSO, i have a working formula:

=COUNTIF(Status:Status, "In Progress")

It wont let me convert to a column formula. Any ideas?

· Share on FacebookShare on Twitter

Best Answer

Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    That's odd. I've re-created your columns in a test sheet and copy/pasted your formulas in directly. Both worked for me and I was able to convert both to a column formula.

    I would suggest reaching out to your account rep and seeing if you can get tech support to take a look.

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

    Hi @Jeff Pindak

    I was also able to use your first formula successfully, with the "Risk Tolerance" column being a Symbol column housing different coloured balls.

    Generally the INVALID OPERATION error seems to signify there's an operator incorrectly used (such as => instead of >=). You can see a list of formula error messages and what they mean in our Help Center (see here).

    Because of this, I'm wondering if anything was added on to your original formula that you didn't post, here? It would be helpful to see a screen capture of your sheet (but please block out any sensitive data), along with confirming what column types are being used (aka what column is the formula being placed in, and what is it looking at).

    In regards to your second formula, it also looks like it's formatted correctly to be a Column Formula. Can you post a screen capture of the error you are receiving? I'm also curious to know why you might want it to be one, as that type of formula would have the exact same result if it's in one cell or in each cell through a whole column.

    Cheers!

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Jeff Pindak
    Jeff Pindak ✭✭✭

    Sure,

    see screen shot below of issue 1

    Screenshot.jpg

    Formula is at sheet level to right. Whats odd is it worked up until about a month ago.

    · Share on FacebookShare on Twitter
  • Jeff Pindak
    Jeff Pindak ✭✭✭

    On second issue, i copied the wrong formula.

    Sorry its this one:

    =IF(AND([Start Date]6 = "", Complete6 = ""), "Un-Scheduled", IF([Start Date]6 = "", "Un-Scheduled", IF(Complete6 = "", "In Progress", "Complete")))

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    https://community.smartsheet.com/discussion/comment/266224#Comment_266224

    For Issue 1, based on this I would scroll through the entire Risk Tolerance column. You are absolutely correct, this formula should work, however if there is an INVALID OPERATION in the column it's referencing (Risk Tolerance), then it will repeat that error instead of providing a result.

    How is your Risk Tolerance column being populated, is this a Nested IF statement? Could it be that around a month ago something happened in that column which now produces an error, and therefore your summary formulas are also showing that error?

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Jeff Pindak
    Jeff Pindak ✭✭✭

    Issue 2 suggestion worked Genevieve, Thanks!


    on Issue 1,

    This formula exists inside Risk Tolerance:

    =IF(AND(TODAY() - [Start Date]2 > 2, ISBLANK(Complete2)), "Red", IF(AND(TODAY() - [Start Date]2 > 1, ISBLANK(Complete2)), "Yellow", "Green"))

    And at Sheet Level, trying to tally them using the issue im referencing here:

    =COUNTIF([Risk Tolerance]:[Risk Tolerance], "Green")

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

    Hi @Jeff Pindak

    Are there any errors throughout your Risk Tolerance column, perhaps way down at the bottom that you can't see right now?

    You could update this column to also be a Column Formula, by adjusting your row references to @row again:

    =IF(AND(TODAY() - [Start Date]@row > 2, ISBLANK(Complete@row)), "Red", IF(AND(TODAY() - [Start Date]@row > 1, ISBLANK(Complete@row)), "Yellow", "Green"))

    That way if anywhere along this column there was an accidental error, this will update the whole column to have this formula. I've confirmed that this is the correct formula structure and doesn't throw an INVALID OPERATION, so this row (row 2) is fine. It's likely that something's going on down at the bottom of your sheet or in a new row that was added in or adjusted around a month ago.

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Jeff Pindak
    Jeff Pindak ✭✭✭

    Once i made it @ row and column formula it did make it easier to hunt down

    the issue.

    Found it, you where correct.


    Thanks so much!

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

    No problem at all! I'm glad we could find the source of the issue. 🙂

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · 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!