如果函数

Sue08
Sue08 ✭
在公式和函数

我只想返回Completed或Not Completed的值。

如果列"Daily" = "#NO MATCH"返回not completed，否则返回completed


我使用

image.png


然而，当每日专栏是“#NO MATCH”时，我没有得到“未完成”。


image.png

非常感谢

· 在Facebook上分享 在Twitter上分享

答案

  • 基督教伯爵
    基督教伯爵 ✭✭✭✭✭

    我不确定你能不能在if语句中使用这样的错误。尝试将daily列中的公式更改为=IFERROR(formula，"")。

    然后，对于每周专栏，您可以使用公式=IF(ISBLANK())(电子邮件保护)) = 0， "已完成"，"未完成")

    · 在Facebook上分享 在Twitter上分享
  • Sue08
    Sue08 ✭

    嗨,基督教

    多谢。

    我想做的是确定哪个国家还没有完成智能表格调查。答复见“国内流离失所者国家程序审查调查”页。在我的计算表上，我列出了我希望完成调查的所有国家。我使用vlookup来检查调查表上列出的国家与我的计算表上的国家列表。我期望的结果是有一列“完成”和“未完成”针对我在计算表上列出的每个国家。

    我希望这是有意义的

    表格1 -表格调查结果

    image.png

    表格2 -计算表

    image.png
    image.png


    image.png


    · 在Facebook上分享 在Twitter上分享
  • 凯利摩尔
    凯利摩尔 ✭✭✭✭✭✭

    嘿@Sue08

    @Christian伯爵的方法将工作-你甚至可以改变基督教的IFERROR稍微取代“”给你“No Match”，你的IF语句可以调整找到。话虽如此，试着调整你当前的IF语句:

    =如果(返回错误((电子邮件保护))、“未完成”、“已完成”)

    这将发现错误。

    凯利

    · 在Facebook上分享 在Twitter上分享
  • Sue08
    Sue08 ✭

    嗨

    我也不能让这个工作。IFERROR语句我不确定

    image.png

    它返回所有国家的“完整”，而不识别不在编辑参考表上的国家

    · 在Facebook上分享 在Twitter上分享
  • 凯利摩尔
    凯利摩尔 ✭✭✭✭✭✭

    嘿,苏

    让我们回顾一下。查看您的屏幕截图，似乎您有其他列/字段依赖于此数据-这将导致错误级联到这些单元格。计算函数通常不会在计算范围内出现错误时执行。由于这些其他字段似乎需要Daily结果，我们必须使用Christian的IFERROR方法来清除错误。

    当您使用IFERROR公式时，您将整个原始公式，括号和所有内容包装在IFERROR()函数中。在插入整个公式之后，您可以添加一个逗号，然后在发生错误时添加您希望发生的任何情况。Christian的公式是用来强制错误的单元格显示一个空白单元格。你可以强迫细胞做任何你喜欢的事。为了使插入文本的位置更明显，我将显示带有“No Match”的公式。为了清楚起见，此公式将放在Daily列中，并替换原来的VLOOKUP公式。

    ＝IFERROR (VLOOKUP([Primary Column]@row，{哪个国家}，1,false)，“不匹配”）

    这就是说，执行VLOOKUP公式，但如果发生任何错误，则插入“No Match”。同样，您可以在引号之间插入任何内容-或者如果您只是使用双引号，其中没有文本，那么将插入一个空白单元格。

    你应该能够做你的IF语句现在在你的[完成/不完成]列-无论你插入的文本现在是可搜索的。

    凯利

    https://help.smartsheet.com/function/iferror


    · 在Facebook上分享 在Twitter上分享
  • Sue08
    Sue08 ✭

    好的，明白了

    谢谢

    · 在Facebook上分享 在Twitter上分享

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

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

交叉表引用的公式组合

智能表功能列表

类别

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

请查看公式手册模板!
It needs to be a countifs instead of countif<\/p>

=COUNTIFS([column4]2:[column4]5,\"Qualification\",[column5]2:[column5]5,\"Q4-2023\")<\/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":106590,"type":"question","name":"Helper checkbox not working on reference sheet","excerpt":"I have multiple line items for a order in one sheet(Request Sheet) and in another sheet (Order sheet) where I have clubbed all the items in single row, based on the order ID from request sheet to order sheet I want to check the Quantity checkbox and update in the Order sheet, I tried index match formula but its not working…","categoryID":322,"dateInserted":"2023-06-19T11:07:26+00:00","dateUpdated":null,"dateLastComment":"2023-06-19T16:56:49+00:00","insertUserID":153826,"insertUser":{"userID":153826,"name":"Domnic Victor","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Domnic%20Victor","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-19T15:46:33+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":144360,"lastUser":{"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-19T22:11:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":37,"score":null,"hot":3374368455,"url":"https:\/\/community.smartsheet.com\/discussion\/106590\/helper-checkbox-not-working-on-reference-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106590\/helper-checkbox-not-working-on-reference-sheet","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106590,"commentID":381185,"name":"Re: Helper checkbox not working on reference sheet","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381185#Comment_381185","dateInserted":"2023-06-19T16:56:49+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-19T22:11: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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/TUXJPHZT569J\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-19T16:01:16+00:00","dateAnswered":"2023-06-19T13:27:24+00:00","acceptedAnswers":[{"commentID":381141,"body":"

The issue is it doesn't know whether to check the box or not check the box because there are multiple ORD0241 on the Request sheet and one is checked and one is not checked. If I am understanding correctly you want the box checked if any of the ORD0241 are checked on the request sheet. If that is correct you would need to use a countifs formula with an If formula<\/p>

=IF(COUNTIFS({Order ID reference from request sheet},[Order ID]@row,{Quantity Helper reference from request sheet},1)>=1,1,0)<\/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":106591,"type":"question","name":"Want to flag overlapping dates AND times","excerpt":"Based on information I've found here, I've created a formula that identifies overlapping times that occur on the same day, but I'd like to adapt it to incorporate items that occur over multiple days. This will be used for booking of equipment. Here is what I have so far: =IF(COUNTIFS(SAP:SAP, SAP@row, [Start Date]:[Start…","categoryID":322,"dateInserted":"2023-06-19T12:43:22+00:00","dateUpdated":null,"dateLastComment":"2023-06-19T15:01:21+00:00","insertUserID":162503,"insertUser":{"userID":162503,"name":"Michelle T","url":"https:\/\/community.smartsheet.com\/profile\/Michelle%20T","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-19T15:03:07+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162503,"lastUser":{"userID":162503,"name":"Michelle T","url":"https:\/\/community.smartsheet.com\/profile\/Michelle%20T","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-19T15:03:07+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":35,"score":null,"hot":3374367883,"url":"https:\/\/community.smartsheet.com\/discussion\/106591\/want-to-flag-overlapping-dates-and-times","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106591\/want-to-flag-overlapping-dates-and-times","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106591,"commentID":381158,"name":"Re: Want to flag overlapping dates AND times","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381158#Comment_381158","dateInserted":"2023-06-19T15:01:21+00:00","insertUserID":162503,"insertUser":{"userID":162503,"name":"Michelle T","url":"https:\/\/community.smartsheet.com\/profile\/Michelle%20T","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-19T15:03:07+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\/8XNE9V76RH1R\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-19T15:03:04+00:00","dateAnswered":"2023-06-19T14:56:38+00:00","acceptedAnswers":[{"commentID":381157,"body":"

Ok I think I got it. It's due to the times you need to combine the dates and times to get it to consider both as a whole and not individually. So you will need a helper column for your start date\/time and end date time. I titled mine as Start Date Time Helper and End Date Time Helper. If you title them the same you should be able to copy and paste formulas.<\/p>

Start Date Time Helper<\/p>

=IFERROR(VALUE(YEAR(DATEONLY([Start Date]@row)) + \"\" + IF(MONTH(DATEONLY([Start Date]@row)) < 10, \"0\") + MONTH(DATEONLY([Start Date]@row)) + IF(DAY(DATEONLY([Start Date]@row)) < 10, \"0\") + DAY(DATEONLY([Start Date]@row)) + IF([Start Time]@row < 1000, \"0\") + [Start Time]@row), \"\")<\/p>

End Date Time Helper <\/p>

=IFERROR(VALUE(YEAR(DATEONLY([End Date]@row)) + \"\" + IF(MONTH(DATEONLY([End Date]@row)) < 10, \"0\") + MONTH(DATEONLY([End Date]@row)) + IF(DAY(DATEONLY([End Date]@row)) < 10, \"0\") + DAY(DATEONLY([End Date]@row)) + IF([End Time]@row < 1000, \"0\") + [End Time]@row), \"\")<\/p>

Formula to get your Yes\/No's<\/p>

=IF(COUNTIFS(SAP:SAP, SAP@row, [End Date Time Helper]:[End Date Time Helper], @cell >= [Start Date Time Helper]@row, [Start Date Time Helper]:[Start Date Time Helper], @cell <= [End Date Time Helper]@row) > 1, \"No\", \"Yes\")<\/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"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&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 in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

公式和函数趋势

  • 请问有人能帮我弄一下这个公式吗

    我试图计算有多少“资格”阶段有一个给定的财政期间，例如第四季度-2023年，我知道我错过了一些东西，但计数的每一个变化，如果我尝试过的公式似乎不起作用，这将是一些非常简单的东西，我试图使复杂，但任何帮助将是……

  • 参考表上的助手复选框不起作用

    我在一个表(请求表)和另一个表(订单表)中有多个订单行项目，其中我已将所有项目合并为单行，基于订单ID从请求表到订单表，我想检查数量复选框并在订单表中更新，我尝试了索引匹配公式，但它不起作用……

  • 想要标记重叠的日期和时间

    根据我在这里找到的信息，我创建了一个公式，用于识别同一天发生的重叠时间，但我想对其进行调整，以合并发生在多天内的项目。这将用于预订设备。这是我到目前为止得到的:=IF(COUNTIFS(SAP:SAP, SAP@row，[开始日期]:[开始…]