CountIFS - last month, current month and NOT cancel
What am I doing wrong?
This is my metrics sheet and I want to count if - last month has a date in the completed column and does NOT has a cancel status.
I will need one for each last month and the current month.
Best Answer
-
Breaking the formula out is a great tip, but I would suggest using a text editor such as Notepad. Programs like Microsoft Word use smart quotes which are going to look slanted or have some other kind of indicator of open vs closed. Smartsheet does not recognize these as valid characters.
"These Quotes" that are straight up and down are the only quotes recognized by Smartsheet.
If breaking down your formula exposes a missing quote and you add it in using Word, you will still get an error because it is the wrong type of quote.
As for the formula... It looks like you may need to get rid of an extra set of parenthesis. Try this:
=COUNTIFS({FY 23 closed}, AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)), {FYStatus}, NOT(@cell = "cancel"))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Try replacing the second AND with a comma.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome thanks - I get unparseable error
=COUNTIFS({FY 23 closed}, AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)), ({FYStatus}, NOT(@cell = "cancel")))
-
Here are a few tricks I've tried.
If possible, break down the formula by parts and see if each one works. Then piece back together.
The next thing I try is opening a word doc and seperate out each function on its own row. This trick would sometimes help me find a missing comma or ().
For all my working formulas, I usually remove the = sign and save the sheet and then add it back. This way the formula is always in the cell history.
Meg Young
mmyoungconsulting@gmail.com
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks! -
@Meg Y - thanks that is a good tip.
-
@Trish Dillon - Let me if one of them works. If you find a different trick please share. Always trying to add to my bag of tricks.
Meg Young
mmyoungconsulting@gmail.com
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks! -
Breaking the formula out is a great tip, but I would suggest using a text editor such as Notepad. Programs like Microsoft Word use smart quotes which are going to look slanted or have some other kind of indicator of open vs closed. Smartsheet does not recognize these as valid characters.
"These Quotes" that are straight up and down are the only quotes recognized by Smartsheet.
If breaking down your formula exposes a missing quote and you add it in using Word, you will still get an error because it is the wrong type of quote.
As for the formula... It looks like you may need to get rid of an extra set of parenthesis. Try this:
=COUNTIFS({FY 23 closed}, AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)), {FYStatus}, NOT(@cell = "cancel"))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome - That explains why I would still get an error when correcting a formula in Word. I'll add that to my tips/tricks sheet. - Meg
Meg Young
mmyoungconsulting@gmail.com
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives