使用COUNTIFS公式
是否有一种方法可以像使用SUMIFS一样使用COUNTIFS函数(或其他函数)?我有一个完美的工作:
=SUMIFS({OM Book Date}， {OM Total Sell}， AND(@cell >= Date (2023,6,1)， @cell <= Date (2023,6,30))))
现在我需要它来计算在同一时间框架内预订的订单的OM Total Sell(有些是空白的，我需要它只计算其中有数字的订单)。
最佳答案
-
保罗新来的 ✭✭✭✭✭✭看起来你可能需要COUNT/DISTINCT/COLLECT组合。
=COUNT(DISTINCT(COLLECT({Work Order}， {Book Date}， AND(IFERROR(MONTH(@cell)， 0) = 6, IFERROR(YEAR(@cell)， 0) = 2023)))))
thinkspi.com
答案
-
保罗新来的 ✭✭✭✭✭✭
-
jtomeaCH ✭抱歉我用错了公式，这个是反的。正确的答案是
=SUMIFS({OM Total Sell}， {OM Book Date}， AND(@cell >= Date (2023,6,1)， @cell <= Date (2023,6,30))))
我的数据的困难之处在于，您可以为项目的每个行设置多个日期(每个PO行上都有入账日期)，但只有一个总销售行。我希望计算总销售线，以便我计算每个单独的项目作为一个，而不是每条线，这将显示更多的项目，我们有由于每个项目的多条线。
-
保罗新来的 ✭✭✭✭✭✭
-
jtomeaCH ✭
由于我的文件有很多列，所以我编了一小段。在这种情况下，我试图使计数返回2的结果。如果我按图书日期计数，我将得到4，但只有2个工作。
-
保罗新来的 ✭✭✭✭✭✭
看起来你可能需要COUNT/DISTINCT/COLLECT组合。
=COUNT(DISTINCT(COLLECT({Work Order}， {Book Date}， AND(IFERROR(MONTH(@cell)， 0) = 6, IFERROR(YEAR(@cell)， 0) = 2023)))))
thinkspi.com
-
jtomeaCH ✭
效果很好。谢谢你！
-
保罗新来的 ✭✭✭✭✭✭
帮助文章参考资料欧宝体育app官方888
If you have a unique ID (like ticket number) that is in both sheets, you could use DataMesh if you have access to that or cross sheet formulas to pull in the data you want based on the matching unique ID.<\/p>
Otherwise, you'd have to use an Add-on. If you are interested in hearing about an add-on that can do this, send me a message at https:\/\/smartsheetguru.com\/contact<\/a><\/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":106845,"type":"question","name":"Count function","excerpt":"Hi I need help with a formula. I have a column that I want to count but only based on a values in a different column. I have tried the following formula but it's not working and I can't figure out what I am doing wrong. =countif([Test Script #]:[Test Script #], [Pass ?]:[Pass ?], <> \"N\/A\") The Test Script # column is the…","categoryID":322,"dateInserted":"2023-06-23T17:50:06+00:00","dateUpdated":null,"dateLastComment":"2023-06-23T19:30:43+00:00","insertUserID":127983,"insertUser":{"userID":127983,"name":"Carol-Anne Cerbone","url":"https:\/\/community.smartsheet.com\/profile\/Carol-Anne%20Cerbone","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!h1jY4Qxc4TY!prNYaQRZvvo!a6wiwaB2GsY","dateLastActive":"2023-06-23T19:29:42+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":127983,"lastUser":{"userID":127983,"name":"Carol-Anne Cerbone","url":"https:\/\/community.smartsheet.com\/profile\/Carol-Anne%20Cerbone","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!h1jY4Qxc4TY!prNYaQRZvvo!a6wiwaB2GsY","dateLastActive":"2023-06-23T19:29:42+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":50,"score":null,"hot":3375096049,"url":"https:\/\/community.smartsheet.com\/discussion\/106845\/count-function","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106845\/count-function","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106845,"commentID":382093,"name":"Re: Count function","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382093#Comment_382093","dateInserted":"2023-06-23T19:30:43+00:00","insertUserID":127983,"insertUser":{"userID":127983,"name":"Carol-Anne Cerbone","url":"https:\/\/community.smartsheet.com\/profile\/Carol-Anne%20Cerbone","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!h1jY4Qxc4TY!prNYaQRZvvo!a6wiwaB2GsY","dateLastActive":"2023-06-23T19:29:42+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-23T19:30:46+00:00","dateAnswered":"2023-06-23T19:28:59+00:00","acceptedAnswers":[{"commentID":382092,"body":"
