Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Formula help

Hello- I'm trying to count number of times text shows in a column and it be a rolling qtr. the column name: "Management code key" and i want to count the number of times "communication" is mentioned. below is my current formula that I'm getting the #unparseable error. formula works without the rolling formula but it counts in all the rows but i need it to be rolling qtr.

=COUNTIF([Management Code Key]:[Management Code Key], FIND("Communication", @cell) > 0, AND([Created]:[Created]@cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 9, 1)), @cell < DATE(YEAR(TODAY()), MONTH(TODAY()), 1))))


TIA.

J

Best Answer

Answers

  • ✭✭✭✭✭

    You have 19 "(" and 20 ")"

    Just a suggestion: if you put this part of the formula in its own column, does it resolve?

    AND([Created]:[Created]@cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 9, 1)), @cell < DATE(YEAR(TODAY()), MONTH(TODAY()), 1))))

    (it might be that you have an extra close paren at the end

  • ✭✭✭✭

    or if there is a better way to pull in by rolling qtr. I'm using the created column as the date.

  • ✭✭✭✭

    nope that wasn't it.. what do you mean with the "you have 19("and 20 ")"?

  • ✭✭✭✭✭✭

    @Jason Jordan He is saying you have 19 open parentheses and 20 closed parentheses. Perhaps you have an extra closed one somewhere and it's messing up your formula.,

  • ✭✭✭✭

    @Mike Wilday ah.. gotcha.. I copied that from another post I'll go through and see if I can find it.

  • ✭✭✭✭

    =COUNTIFS([Management Code Key]:[Management Code Key], FIND("Communication", @cell) > 0) this formula works but I'm still having trouble adding the date criteria to look back 90 days rolling anyone have any suggestions to get it to work?

  • ✭✭✭✭
    Answer ✓

    I think i figured it out:

    =COUNTIFS([Management Code Key]:[Management Code Key], FIND("Communication", @cell) > 0, Created:Created, >=TODAY(-90))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions