公式显示问题

NuGenAdmin
NuGenAdmin ✭
在公式和函数

我有另一个等式没有显示我需要下拉的值。我想我的公式是对的，它没有返回错误，但是没有数字显示。最后，我希望它能把前一年的数据拉下来，这样我们就可以比较了。思想/想法吗?


image.png


· 在Facebook上分享 在Twitter上分享

最佳答案

  • 凯利摩尔
    凯利摩尔 ✭✭✭✭✭✭
    编辑06/04/23 ✓回答

    嘿@NuGenAdmin

    我不太明白你想达到什么目的。试试这个，看看你的猜测是否正确。

    我故意省略IFERROR，直到公式正常工作，因为它将掩盖公式生成的任何错误。

    =INDEX(收集([也能源生产]:[也能源生产]，年:ob欧宝娱乐app手机下载年，[前一年]@行，月:月，(电子邮件保护)，[项目名称]:[项目名称]，[项目名称]@row)，1)

    这是你需要的吗?

    凯利

    · 在Facebook上分享 在Twitter上分享

答案

帮助文章参考资料欧宝体育app官方888

在智能表中创建和编辑公式

交叉表引用的公式组合

智能表功能列表

类别

想要直接在智能表中练习使用公式吗?

请查看公式手册模板!
0, [% Complete]@row < 1), \"In Progress\", IF([% Complete]@row = 0, \"Not Started\"))) I want to add in…","categoryID":322,"dateInserted":"2023-06-07T14:52:30+00:00","dateUpdated":null,"dateLastComment":"2023-06-07T15:16:49+00:00","insertUserID":157541,"insertUser":{"userID":157541,"name":"ro.fei","title":"","url":"https:\/\/community.smartsheet.com\/profile\/ro.fei","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/4OWA5DDSR8JB\/n2H4UP1N6TZA6.jpg","dateLastActive":"2023-06-07T17:59:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":157541,"lastUser":{"userID":157541,"name":"ro.fei","title":"","url":"https:\/\/community.smartsheet.com\/profile\/ro.fei","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/4OWA5DDSR8JB\/n2H4UP1N6TZA6.jpg","dateLastActive":"2023-06-07T17:59:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":18,"score":null,"hot":3372301159,"url":"https:\/\/community.smartsheet.com\/discussion\/106137\/trying-to-determine-if-all-children-have-n-a-status","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106137\/trying-to-determine-if-all-children-have-n-a-status","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106137,"commentID":379294,"name":"Re: Trying to determine if all children have N\/A Status","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379294#Comment_379294","dateInserted":"2023-06-07T15:16:49+00:00","insertUserID":157541,"insertUser":{"userID":157541,"name":"ro.fei","title":"","url":"https:\/\/community.smartsheet.com\/profile\/ro.fei","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/4OWA5DDSR8JB\/n2H4UP1N6TZA6.jpg","dateLastActive":"2023-06-07T17:59:33+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-07T15:16:56+00:00","dateAnswered":"2023-06-07T15:16:49+00:00","acceptedAnswers":[{"commentID":379294,"body":"

I got it! It's long but it works:<\/p>


<\/p>

=IF(AND(CONTAINS(\"N\/A\", CHILDREN(STATUS@row)), CONTAINS(\"Not Started\", CHILDREN(STATUS@row)) = false, CONTAINS(\"In Progress\", CHILDREN(STATUS@row)) = false, CONTAINS(\"Complete\", CHILDREN(STATUS@row)) = false), \"N\/A\", IF([% Complete]@row = 1, \"Complete\", IF(AND([% Complete]@row > 0, [% Complete]@row < 1), \"In Progress\", IF([% Complete]@row = 0, \"Not Started\"))))<\/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":106136,"type":"question","name":"I am trying to manipulate my SUMIFs Formula","excerpt":"When Using SUMIFS(Range, Criterion Range 1,Criterion...) I have multiple columns for example Estimate Spend, 25% spend, 50% spend, Rather than having multiple %'s of spend I just want to be able to use 1 reference and multiply it by 0.25 or 0.5 in the first \"Range\" category so =SUMIFS({Spend} * 0.25, {Phase}, \"phase 1\",…","categoryID":322,"dateInserted":"2023-06-07T14:39:19+00:00","dateUpdated":null,"dateLastComment":"2023-06-07T14:56:44+00:00","insertUserID":155241,"insertUser":{"userID":155241,"name":"Mathew Palter","url":"https:\/\/community.smartsheet.com\/profile\/Mathew%20Palter","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxYml_p2w4ZGEQsYcizl1E0LV2zWrFZLuG5E2D3h=s96-c","dateLastActive":"2023-06-07T16:05:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":155241,"lastUser":{"userID":155241,"name":"Mathew Palter","url":"https:\/\/community.smartsheet.com\/profile\/Mathew%20Palter","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxYml_p2w4ZGEQsYcizl1E0LV2zWrFZLuG5E2D3h=s96-c","dateLastActive":"2023-06-07T16:05:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":22,"score":null,"hot":3372299763,"url":"https:\/\/community.smartsheet.com\/discussion\/106136\/i-am-trying-to-manipulate-my-sumifs-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106136\/i-am-trying-to-manipulate-my-sumifs-formula","format":"Rich","tagIDs":[219,254,319,334,445],"lastPost":{"discussionID":106136,"commentID":379290,"name":"Re: I am trying to manipulate my SUMIFs Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379290#Comment_379290","dateInserted":"2023-06-07T14:56:44+00:00","insertUserID":155241,"insertUser":{"userID":155241,"name":"Mathew Palter","url":"https:\/\/community.smartsheet.com\/profile\/Mathew%20Palter","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxYml_p2w4ZGEQsYcizl1E0LV2zWrFZLuG5E2D3h=s96-c","dateLastActive":"2023-06-07T16:05:29+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-07T14:56:07+00:00","dateAnswered":"2023-06-07T14:43:39+00:00","acceptedAnswers":[{"commentID":379282,"body":"

@Mathew Palter<\/a> <\/p>

Would =SUMIFS({Spend}, {Phase}, \"phase 1\", {Status}, \"IN PROGRESS\")*0.25 not work? It just takes the sum then multiply it by 25%. You can do the same for 50%<\/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":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":319,"urlcode":"functionality","name":"functionality"},{"tagID":334,"urlcode":"automations","name":"Automations"},{"tagID":445,"urlcode":"importing-and-exporting","name":"Importing and Exporting"}]},{"discussionID":106129,"type":"question","name":"Quiz scoring","excerpt":"Need a proper formula to count each correct answer on a row. with inspections it is safe or at risk which is easy to count, but i can figure out how to count each column with different answer. I came up with this =COUNTIFS([Reason forklift safety is important]@row, \"D. All of the above\", [Primary sources of fuel]@row, \"D.…","categoryID":322,"dateInserted":"2023-06-07T13:54:27+00:00","dateUpdated":null,"dateLastComment":"2023-06-07T15:00:24+00:00","insertUserID":132359,"insertUser":{"userID":132359,"name":"Kyle Black","url":"https:\/\/community.smartsheet.com\/profile\/Kyle%20Black","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3Zddf9L-k7w!8sDQ90it9NQ!7sc5AJ6VerR","dateLastActive":"2023-06-07T16:50:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":132359,"lastUser":{"userID":132359,"name":"Kyle Black","url":"https:\/\/community.smartsheet.com\/profile\/Kyle%20Black","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3Zddf9L-k7w!8sDQ90it9NQ!7sc5AJ6VerR","dateLastActive":"2023-06-07T16:50:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":23,"score":null,"hot":3372297291,"url":"https:\/\/community.smartsheet.com\/discussion\/106129\/quiz-scoring","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106129\/quiz-scoring","format":"Rich","lastPost":{"discussionID":106129,"commentID":379293,"name":"Re: Quiz scoring","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379293#Comment_379293","dateInserted":"2023-06-07T15:00:24+00:00","insertUserID":132359,"insertUser":{"userID":132359,"name":"Kyle Black","url":"https:\/\/community.smartsheet.com\/profile\/Kyle%20Black","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3Zddf9L-k7w!8sDQ90it9NQ!7sc5AJ6VerR","dateLastActive":"2023-06-07T16:50:22+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/KARXKDBS5IKA\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-07T14:59:49+00:00","dateAnswered":"2023-06-07T14:11:48+00:00","acceptedAnswers":[{"commentID":379271,"body":"

=COUNTIF([Primary sources of fuel]@row, \"D. All of the above\") + COUNTIF([Operator is not required to wear seat belt]@row, \"E. The operator is alwa...) + COUNTIF([major difference between car and forklift]@row, \"D. All of the above\") + ...<\/p>

You should be able to finish the formula based on this. This will work, but this will be a very long formula. Maybe someone can suggest a more concise approach.<\/p>


<\/p>

COUNTIFS is not meant to count multiple things. Its purpose is to verify that something meets multiple criteria before being counted.<\/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":[]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

公式和函数趋势

  • 试图确定是否所有子节点都具有“N/A状态”

    我正在研究汇总行的公式，以查看行的% Complete以及子行的状态。我有%完成部分处理:=IF([% Complete]@row = 1，“完成”，如果(AND([% Complete]@row > 0， [% Complete]@row < 1)，“正在进行中”，如果([% Complete]@row = 0，“未开始”)))我想添加…

  • 我在试着修改我的sumif公式

    当使用SUMIFS(范围，标准范围1，标准…)我有多个列，例如估算花费，25%花费，50%花费，而不是花费的多个%，我只想使用一个参考并将其乘以0.25或0.5在第一个“范围”类别中=SUMIFS({Spend} * 0.25， {Phase}，“Phase 1”，……

  • 测试得分

    需要一个合适的公式来计算每一行的正确答案。有了检查，它是安全的还是有风险的，这很容易计数，但我可以计算出如何用不同的答案来计数每一列。我想出了这个=COUNTIFS([叉车安全很重要的原因]@row，“D以上所有”，[燃料的主要来源]@row，“D....