SUMIFS公式不可解析
你好,
我下面的公式总是无法解释，我不知道为什么…需要求和的列首先在Range 2中被引用。我还引用了月份和年份标准的相同日期列。就在我嘴边，但我已经没有选择了。这里有任何帮助就太好了。当选择AUTO时，我需要范围2值求和，并且日期在2019年1月的任何时间着陆。
=SUMIFS({Peterson Family Transactions Range 2}， {Peterson Family Transactions Range 1}， "AUTO"， {Peterson Family Transactions Range 3}， MONTH()@CELL)= 1， {Peterson Family Transactions Range 4}， YEAR(@CELL)= 2019)
提前感谢你的帮助:)
评论
-
保罗新来的 ✭✭✭✭✭✭如果您引用月份和年份的相同日期列，则应该是相同的范围(3或4)。我会首先验证这一点。我发现有助于避免这种情况的一件事是，当我为多组标准引用相同的范围时，使用AND或or函数。我还将我的范围重命名为“表名-列名”，以便在遇到问题时更容易引用。
首先仔细检查你的日期列范围，如果你愿意的话(所有这些混乱的总结在底部)…
=SUMIFS({Peterson Family Transactions Range 2}， {Peterson Family Transactions Range 1}， "AUTO"， {Peterson Family Transactions Range 3}，(月(@CELL)= 1, year (@cell)= 2019)
．
我还发现在公式中使用单元格引用而不是硬数据很有用。如果我一次只需要查看一个月，我会在工作表的其他地方使用两个单独的单元格，这样我就可以在不编辑公式本身的情况下更改月份和年份。
=条件求和({彼得森家族交易-金额范围}，{Peterson Family Transactions - Type}“汽车”,{Peterson Family Transactions - Date}，(月(@cell)＝(帮助列)1美元年(@cell)＝(帮助列)2美元））
如果我要做一个比较或年至今的跟踪，我会建立一个表。具体的需求将决定实际的布局，但是会有一列或一行列出月份。然后，我将使用辅助列或行，并将月份数字放在那里，然后在公式中引用该列或行。因此，假设我的月份在Month列下，我的月份数字在一个名为[Month Number]的隐藏列中，我使用Month列的第1行输入我的日期，这个表看起来是这样的……
月数月数(隐藏)
2019
1月1日
2月2日
3月3日
我的公式是这样的
=SUMIFS({Peterson Family Transactions - Sum Range}， {Peterson Family Transactions - Type}， "AUTO"， {Peterson Family Transactions - Date}， AND(MONTH)@cell)＝美元(月数)@row年(@cell)＝月1美元））
．
对于多种类型…
类型1月2月3月4月
2019 1 2 3 4
A f f
B
然后在二月使用(左上角F)
=SUMIFS({Peterson Family Transactions - Sum Range}， {Peterson Family Transactions - Type}，(电子邮件保护)， {Peterson Family Transactions - Date}， AND(月)@cell)＝1月1美元年(@cell)＝类型1美元））
．
注意使用$来锁定列和行引用。这将允许您将公式放在第一个单元格中，并通过上下拖动填充填充表的其余部分。
．
如果你已经读到这里(或者只是跳到最后)，这里是总结……
尝试设置一些东西，以便您可以尽可能少地直接编辑公式本身，并尝试在可能的情况下通过使用and和OR函数来压缩公式，当相同的范围将用于多个标准时。
thinkspi.com
-
daniel68616 ✭✭✭
谢谢你的建议，保罗。这很有帮助，告诉我需要简化公式。我已经尝试了AND功能，就像你提到的，我仍然没有运气…我想不明白。我有类似的公式，就像这个一样，效果很好:(。我也核实了我所有的推荐信都是正确的。
-
保罗新来的 ✭✭✭✭✭✭
你能提供一些截图吗?
我提到参考的原因是因为你提到你正在查看月份和年份的相同范围，但是你的范围被标记为3和4。除非您特别更改了范围名称，否则它们应该是相同的。要么都是3，要么都是4。
我注意到，有时当我做交叉表参考时，如果我移动太快，计算机或SS有点慢，我会选择一行或一列，然后范围跳回到主单元格[列1]1。然后，我必须确保重新选择正确的范围，否则公式将无法正常工作。
thinkspi.com
-
daniel68616 ✭✭✭
嗨,保罗,
所以这里是目前的公式，我尝试使用你的建议，仍然没有运气。我还认为奇怪的是，我引用的相同日期列出现了不同的范围数字，所以我认为我会消除这种可能性。然而，在进行此更改之前，我确实检查了一下，出于某种原因，我仍然想将其指定为不同的数字:/
=SUMIFS({Peterson Family Transactions Range 2}， {Peterson Family Transactions Range 3}， "AUTO"， {Peterson Family Transactions Range 1}， AND(@CELL)= 1, year (@CELL)= 2019)
下面是我按照公式中列出的顺序引用的每个范围的屏幕截图。希望这能有所帮助
帮助文章参考资料欧宝体育app官方888
类别
Try this<\/p>
=IF(Available@row > 0, \"PES\", JOIN(INDEX(COLLECT(Facility:Facility, [Microscope: 1]:[Microscope: 1], HAS(@cell,\"6633112443\")), 1)))<\/p>
Will this work for you?<\/p>
Kelly<\/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":106545,"type":"question","name":"Automatically Delete Attachments when a condition is met?","excerpt":"Hello, I am looking for a way to automatically delete attachments on a row when the work is approved. Is this possible?","categoryID":322,"dateInserted":"2023-06-16T13:00:01+00:00","dateUpdated":"2023-06-16T13:00:52+00:00","dateLastComment":"2023-06-19T12:21:53+00:00","insertUserID":162459,"insertUser":{"userID":162459,"name":"Krys West","title":"Office Manager","url":"https:\/\/community.smartsheet.com\/profile\/Krys%20West","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-19T12:21:28+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":162459,"lastUser":{"userID":162459,"name":"Krys West","title":"Office Manager","url":"https:\/\/community.smartsheet.com\/profile\/Krys%20West","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-19T12:21:28+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":29,"score":null,"hot":3374099514,"url":"https:\/\/community.smartsheet.com\/discussion\/106545\/automatically-delete-attachments-when-a-condition-is-met","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106545\/automatically-delete-attachments-when-a-condition-is-met","format":"Rich","lastPost":{"discussionID":106545,"commentID":381128,"name":"Re: Automatically Delete Attachments when a condition is met?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381128#Comment_381128","dateInserted":"2023-06-19T12:21:53+00:00","insertUserID":162459,"insertUser":{"userID":162459,"name":"Krys West","title":"Office Manager","url":"https:\/\/community.smartsheet.com\/profile\/Krys%20West","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-19T12:21:28+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-19T12:22:43+00:00","dateAnswered":"2023-06-16T18:43:16+00:00","acceptedAnswers":[{"commentID":381061,"body":"
Hi @Krys West<\/a> <\/p> I hope you're well and safe!<\/p> Unfortunately, it's not possible now, but it's an excellent idea!<\/p> Please submit this as a Product Feedback or Idea <\/strong>(If it hasn't been added already)<\/em><\/strong> when you have a moment.<\/strong><\/a><\/p> I hope that helps!<\/p> Be safe, and have a fantastic weekend!<\/p> Best,<\/p> Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a> <\/p> ✅Please support the Community by marking the post(s)<\/em> <\/em><\/strong>that<\/em> <\/em><\/strong>helped or answered your question or solved your problem with <\/em>the accepted answer\/helpful, Insightful\/Vote Up\/Awesome<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":106579,"type":"question","name":"Split Text","excerpt":"Can someone assist me? I need a formula on how to split the text for this below. Ex. LOW - VS-87 - Service-FS Battery Replacement Lead-MEGA MALL-1259410 I need a to have a column that would write MEGA MALL only.","categoryID":322,"dateInserted":"2023-06-17T17:04:55+00:00","dateUpdated":null,"dateLastComment":"2023-06-18T01:39:56+00:00","insertUserID":103391,"insertUser":{"userID":103391,"name":"Marilen.Navarro103391","url":"https:\/\/community.smartsheet.com\/profile\/Marilen.Navarro103391","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-09\/60\/88\/n6088a8f5166f388fd95cfcb413bcbd0e.jpg","dateLastActive":"2023-06-18T01:41:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":112221,"lastUser":{"userID":112221,"name":"Kelly Moore","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Moore","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jnPa4zng1Pw!n1lbH6Wxgbo!UbxYNkVsKJw","dateLastActive":"2023-06-19T02:46:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":32,"score":null,"hot":3374075691,"url":"https:\/\/community.smartsheet.com\/discussion\/106579\/split-text","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106579\/split-text","format":"Rich","lastPost":{"discussionID":106579,"commentID":381096,"name":"Re: Split Text","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381096#Comment_381096","dateInserted":"2023-06-18T01:39:56+00:00","insertUserID":112221,"insertUser":{"userID":112221,"name":"Kelly Moore","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Moore","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jnPa4zng1Pw!n1lbH6Wxgbo!UbxYNkVsKJw","dateLastActive":"2023-06-19T02:46:11+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-18T01:42:10+00:00","dateAnswered":"2023-06-18T01:39:56+00:00","acceptedAnswers":[{"commentID":381096,"body":"
