具有来自多个列的多个条件的COUNTIFS
你好,
我在这里尝试过各种不同的公式，但仍然无法解析。
我试图创建一个公式，从另一个表链接，找到以下标准:
如果这些值的行包含任何:“未着手没有开始”,“進行中在进步”,“現場確認审查”,“保留中(予算)暂停(资金)”,“保留中搁置”列的{状態地位}包含“例二”列和行{エリア区域}。
任何帮助都是感激的!
最佳答案
-
保罗新来的 ✭✭✭✭✭✭它看起来是这样的:
=条件统计({状態地位}、(@cell =“未着手没有开始”,@cell =“進行中在进步”,@cell =“現場確認审查”,@cell =“保留中(予算)暂停(资金)”,@cell =“保留中搁置”),{エリア区域},@cell =“例二”)
thinkspi.com
答案
-
保罗新来的 ✭✭✭✭✭✭
它看起来是这样的:
=条件统计({状態地位}、(@cell =“未着手没有开始”,@cell =“進行中在进步”,@cell =“現場確認审查”,@cell =“保留中(予算)暂停(资金)”,@cell =“保留中搁置”),{エリア区域},@cell =“例二”)
thinkspi.com
-
保罗新来的 ✭✭✭✭✭✭
-
CRUSJ ✭
帮助文章参考资料欧宝体育app官方888
类别
=COUNTIFS({状態 Status}, OR(@cell = \"未着手 Not Started\", @cell = \"進行中 In Progress\", @cell = \"現場確認 Field Review\", @cell = \"保留中（予算）On Hold (Funding)\", @cell = \"保留中 On Hold\"), {エリア Area}, @cell = \"Example1\")<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":105974,"type":"question","name":"Help creating a formula that generates the current fiscal year","excerpt":"The following formula is #UNPARSEABLE. I'm not sure why. Please help. =IF(MONTH(TODAY())>=7, YEAR(TODAY())&\"-\"&(YEAR(TODAY())+1), (YEAR(TODAY())-1)&\"-\"&YEAR(TODAY()))","categoryID":322,"dateInserted":"2023-06-02T19:06:56+00:00","dateUpdated":null,"dateLastComment":"2023-06-02T19:46:15+00:00","insertUserID":161705,"insertUser":{"userID":161705,"name":"Christina S.","url":"https:\/\/community.smartsheet.com\/profile\/Christina%20S.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-02T19:44:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161705,"lastUser":{"userID":161705,"name":"Christina S.","url":"https:\/\/community.smartsheet.com\/profile\/Christina%20S.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-02T19:44:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":28,"score":null,"hot":3371469191,"url":"https:\/\/community.smartsheet.com\/discussion\/105974\/help-creating-a-formula-that-generates-the-current-fiscal-year","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/105974\/help-creating-a-formula-that-generates-the-current-fiscal-year","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":105974,"commentID":378672,"name":"Re: Help creating a formula that generates the current fiscal year","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/378672#Comment_378672","dateInserted":"2023-06-02T19:46:15+00:00","insertUserID":161705,"insertUser":{"userID":161705,"name":"Christina S.","url":"https:\/\/community.smartsheet.com\/profile\/Christina%20S.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-02T19:44:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-02T19:45:43+00:00","dateAnswered":"2023-06-02T19:21:45+00:00","acceptedAnswers":[{"commentID":378667,"body":"
The concatenate operator in Smartsheet is + instead of &.<\/p>
<\/p>
=IF(MONTH(TODAY())>=7, YEAR(TODAY())+\"-\"+(YEAR(TODAY())+1), (YEAR(TODAY())-1)+\"-\"+YEAR(TODAY()))<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":105970,"type":"question","name":"Trying to evaluate multiple columns with an IF statement","excerpt":"I keep getting a syntax error for some reason but can't figure out why. I am basically trying to set a status icon of 'Sunny\" if the status of the risk is \"closed\" and if not closed, i am using the other icons based on the level of the risk. here is the formula: =IF([Status]@row = “Closed”, “sunny”, IF([Risk Score]@row <=…","categoryID":322,"dateInserted":"2023-06-02T17:58:00+00:00","dateUpdated":null,"dateLastComment":"2023-06-02T19:19:11+00:00","insertUserID":162035,"insertUser":{"userID":162035,"name":"Drew N.","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Drew%20N.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-02T19:20:11+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-02T20:54:24+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3371464031,"url":"https:\/\/community.smartsheet.com\/discussion\/105970\/trying-to-evaluate-multiple-columns-with-an-if-statement","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/105970\/trying-to-evaluate-multiple-columns-with-an-if-statement","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":105970,"commentID":378663,"name":"Re: Trying to evaluate multiple columns with an IF statement","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/378663#Comment_378663","dateInserted":"2023-06-02T19:19:11+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-02T20:54:24+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-02T18:44:44+00:00","dateAnswered":"2023-06-02T18:24:38+00:00","acceptedAnswers":[{"commentID":378641,"body":"
I think you are overcomplicating it. Try this instead:<\/p>
=IF([Status]@row = \"Closed\", \"sunny\", IF([Risk Score]@row <= 25, \"partly sunny\", IF([Risk Score]@row <= 50, \"cloudy\", IF([Risk Score]@row <= 90, \"rainy\", \"stormy\"))))<\/p>
<\/p>
It also looks like some of your quotes are slanted and others are straight up and down. The slanted ones are called \"Smart Quotes\" which (ironically enough) are not recognized as valid characters in Smartsheet formulas. You will need to either retype them here in the Community (which I did above already), directly in Smartsheet, or in a text editor such as Notepad (not Word).<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
