Formula help

Jason Jordan · 07/29/21

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

Jason Jordan · 07/29/21

I think i figured it out:

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

James Keuning · 07/29/21

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

Jason Jordan · 07/29/21

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

Jason Jordan · 07/29/21

https://community.smartsheet.com/discussion/comment/299532#Comment_299532

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

Mike Wilday · 07/29/21

@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.,

Jason Jordan · 07/29/21

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

Jason Jordan · 07/29/21

=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?

Jason Jordan · 07/29/21

I think i figured it out:

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

Formula help

Best Answer

Answers

Help Article Resources

Categories