COUNTIFS function
Hi All,
I have problem with Countifs in Smartsheet during it's still workable in Excell as well.
As below picture, I'd like to count product type is "Goalies Mask" with result "FAIL". I applied formula : =COUNTIFS([Product type]:[Product type],"Goalie Mask",[Result]1:[Result],"FAIL"))
but it shows #UNPARSEABLE.
Would you please to help me correct again formula if i'm wrong! Thank you!
Best Answers
-
Andrée Starå ✭✭✭✭✭✭Hi Tony,
You have the number 1 in the Result range and one to many closing parentheses in the end. Remove those, and it will work. Also, you can remove the [ ] around the Result because it’s one word and has no numbers or special characters.
Edit: Looking at the screenshot, it seems like you only need to remove the last parenthesis.
Try this.
=COUNTIFS([Product type]:[Product type], "Goalie Mask", Result:Result, "FAIL")
Did that work?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @WORK BOLD
✅我的帖子(s) help or answer your question or solve your problem? Please support the Community bymarking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:(email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Andrée Starå ✭✭✭✭✭✭
Try something like this.
You actually don't need the Week column for the formula to work. At the end of the formula I added -0 and you can change that to -1 to look at last week. Would that work?
COUNTIFS
=COUNTIFS([Product type]:[Product type]; "Goalie Mask"; [Inspection Date]:[Inspection Date]; IFERROR(WEEKNUMBER(@cell); 0) = WEEKNUMBER(TODAY()) - 0)
SUMIFS
=条件求和([主要缺陷数量]:[主要缺陷数量);(Inspection Date]:[Inspection Date]; IFERROR(WEEKNUMBER(@cell); 0) = WEEKNUMBER(TODAY()) - 0)
Did they work?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:(email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
Answers
-
Andrée Starå ✭✭✭✭✭✭
Hi Tony,
You have the number 1 in the Result range and one to many closing parentheses in the end. Remove those, and it will work. Also, you can remove the [ ] around the Result because it’s one word and has no numbers or special characters.
Edit: Looking at the screenshot, it seems like you only need to remove the last parenthesis.
Try this.
=COUNTIFS([Product type]:[Product type], "Goalie Mask", Result:Result, "FAIL")
Did that work?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @WORK BOLD
✅我的帖子(s) help or answer your question or solve your problem? Please support the Community bymarking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:(email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Hi Andrée Starå,
Appreciated your help! It works perfect after your advice!
Thank you so much!
-
Andrée Starå ✭✭✭✭✭✭
Excellent!
You're more than welcome!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:(email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Hi Andrée Starå,
One more things I need from your help!
Kindly see below picture:
- I want to Sum data of product Goalie Mask with Major issue in Week 19. Current I setup manual formula to sort week 19 as : =SUMIFS([MAJOR defects Qty]:[MAJOR defects Qty]; Week:Week; "19"; [Product type]:[Product type]; "Goalie Mask"). It work as well. But I would to auto update last 7 days to replace manual sorting as formula: =SUMIFS([MAJOR defects Qty]:[MAJOR defects Qty]; WEEKNUMBER(TODAY()-7; [Product type]:[Product type]; "Goalie Mask"), It was not work. Would you please to help me correct again
- It also the same with COUNTIFS when I want to replace manual Count data of product Goalies mask with last 7 days as formula: =COUNTIFS([Product type]:[Product type]; "Goalie Mask"; WEEKNUMBER(TODAY()-7)
Thank you for your help!
-
Andrée Starå ✭✭✭✭✭✭
Try something like this.
You actually don't need the Week column for the formula to work. At the end of the formula I added -0 and you can change that to -1 to look at last week. Would that work?
COUNTIFS
=COUNTIFS([Product type]:[Product type]; "Goalie Mask"; [Inspection Date]:[Inspection Date]; IFERROR(WEEKNUMBER(@cell); 0) = WEEKNUMBER(TODAY()) - 0)
SUMIFS
=条件求和([主要缺陷数量]:[主要缺陷数量);(Inspection Date]:[Inspection Date]; IFERROR(WEEKNUMBER(@cell); 0) = WEEKNUMBER(TODAY()) - 0)
Did they work?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:(email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Dear Andrée Starå,
Thank you so much for your supporting! Everything is perfect as I expected
Best regards,
Tony
-
Andrée Starå ✭✭✭✭✭✭
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:(email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
Help Article Resources
Categories
Try this:<\/p>
=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"//www.santa-greenland.com/community/discussion/68305/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108267,"type":"question","name":"Combining IF Formula for Blank\/ Not Blank Cells","excerpt":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected Date\" null -Pending: Based on \"Anticipated Collcted Date\" not null and \"Collected Date\" null Below is what I have, but it's unparseable:…","snippet":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected…","categoryID":322,"dateInserted":"2023-07-28T17:23:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164288,"lastUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":42,"score":null,"hot":3381135147,"url":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","format":"Rich","lastPost":{"discussionID":108267,"commentID":387885,"name":"Re: Combining IF Formula for Blank\/ Not Blank Cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387885#Comment_387885","dateInserted":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T18:30:11+00:00","dateAnswered":"2023-07-28T18:22:11+00:00","acceptedAnswers":[{"commentID":387882,"body":"
