在子中计算特定列表项
我有一个正在发送给特定用户的报告列表，如下所示:
位置1
—用户1 xxxxxx
——周一报告1
——周四报告2
——每日报告
在“xxxxxxx”单元格中，我想要这样写:M:2 T:0 W:0 T:2 F:0
让我一眼就知道某人每天收到多少报告。我不能展开工作表来使用复选框，它都需要在一个单元格中。是否有一种方法可以添加星期一和每日的每个实例，并使用连接公式将它们放在一起?
评论
-
特拉维斯 员工这可以使用COUNTIF和concatate函数来完成。
这是公式。如果XXXXXX位于父行中，而报表1、报表2等是子行，则可以将此公式复制并粘贴到工作表中。如果不使用层次结构，则需要将所有CHILDREN()函数替换为单元格范围。
此公式计算每天出现的时间数+如果每天出现，将它们加在一起并将其与文本连接。
= "男:" +(条件统计(儿童(),“周一”)+(条件统计(儿童(),“日常”)))+“T:”+(条件统计(儿童(),“周二”)+(条件统计(儿童(),“日常”)))+“W:”+(条件统计(儿童(),“星期三”)+(条件统计(儿童(),“日常”)))+“T:”+(条件统计(儿童(),“星期四”)+(条件统计(儿童(),“日常”)))+“F:”+(条件统计(儿童(),“星期五”)+(条件统计(儿童(),“日常”)))
结果是(使用您的示例):M:2 T:1 W:1 T:2 F:1
通过我们的帮助中心页面了解更多关于Smartsheet中的公式:http://help.smartsheet.com/customer/portal/articles/775363-using-formulas
-
哦，哇，太完美了!非常感谢!
类别
Like that it is working partially, because as I have sub assemblies, I will need to take the quantity of the PARENT() and multiply it to have the correct value.<\/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":106656,"type":"question","name":"Incorrect Argument - Second set of eyes please","excerpt":"I'm not sure what I'm missing but would appreciate some help with the below formula it is giving me an incorrect argument set and I can't figure out why. =COUNTIFS({Greet}, OR(@cell = \"yes\", @cell = \"NA\"), {Week Beginning}, [Report Date]@row, {Monitor Type}, \"Monitor\") \/ COUNTIFS({Greet}, OR(@cell = \"yes\", @cell = \"no\",…","categoryID":322,"dateInserted":"2023-06-20T18:34:48+00:00","dateUpdated":null,"dateLastComment":"2023-06-21T12:22:01+00:00","insertUserID":144360,"insertUser":{"userID":144360,"name":"Hollie Green","url":"https:\/\/community.smartsheet.com\/profile\/Hollie%20Green","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-21T12:10:09+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-21T12:20:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3374638009,"url":"https:\/\/community.smartsheet.com\/discussion\/106656\/incorrect-argument-second-set-of-eyes-please","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106656\/incorrect-argument-second-set-of-eyes-please","format":"Rich","lastPost":{"discussionID":106656,"commentID":381461,"name":"Re: Incorrect Argument - Second set of eyes please","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381461#Comment_381461","dateInserted":"2023-06-21T12:22:01+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-21T12:20:27+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-21T12:12:06+00:00","dateAnswered":"2023-06-21T12:01:53+00:00","acceptedAnswers":[{"commentID":381446,"body":" Syntax is right, so the other thing that can cause that error is range sizes not matching. Double check your cross sheet references to ensure they are all the same size and shape.<\/p> I have trouble sometimes with this when I am referencing a large sheet or my internet is behaving poorly. Sometimes it takes a few extra seconds for the sheet to fully load in when you are selecting the range for the cross sheet reference. When the sheet does fully load in, the selection will always revert to the \"home cell\" or the cell in the top right corner. Even if you have already clicked on a column header. Then I have to go back and edit the reference and pause for a few seconds to let the sheet load into the window. You should be good to go when the sheet name appears near the top left of the window.<\/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":106634,"type":"question","name":"Formula to set the Task Health.","excerpt":"Hi Good Morning, I have a question on this formula. Can you tell me what I did wrong please? Thanks for your great help.","categoryID":321,"dateInserted":"2023-06-20T14:28:24+00:00","dateUpdated":null,"dateLastComment":"2023-06-21T12:22:55+00:00","insertUserID":162441,"insertUser":{"userID":162441,"name":"AAL","title":"Automation - Record a Date","url":"https:\/\/community.smartsheet.com\/profile\/AAL","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-21T12:10:40+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-21T12:20:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":10,"countViews":56,"score":null,"hot":3374627479,"url":"https:\/\/community.smartsheet.com\/discussion\/106634\/formula-to-set-the-task-health","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106634\/formula-to-set-the-task-health","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106634,"commentID":381462,"name":"Re: Formula to set the Task Health.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381462#Comment_381462","dateInserted":"2023-06-21T12:22:55+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-21T12:20:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/Y9UHM44PUCBT\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-20T18:05:21+00:00","dateAnswered":"2023-06-20T17:22:23+00:00","acceptedAnswers":[{"commentID":381355,"body":" Happy to help. 👍️<\/span><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&siteSectionID=0&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending Posts","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
=SUMIF([Part number]$185:[Part number]$369, CONTAINS([Part number]167, @cell), Quantity$185:Quantity$369)<\/code><\/strong><\/p>
<\/p>
