英文:
Excel SUMIFS throws #Error for two criterias and =MONTH function
问题
我在Excel中有以下情况，其中我根据日期和任务记录了小时数。
根据=UNIQUE()，我在I列中添加了所有我的任务，在J列中，我试图根据任务和在I2中定义的当前月份添加小时数。
这个公式会给我返回#Error，但我真的无法理解为什么。
谢谢您的时间！
英文:
I have the following case in Excel where I have hours based on date and task.
Based on an =UNIQUE() I add all my tasks in I column and in J column I am trying to add the hours based the task and the current month defined on I2.
That formula will throw me the #Error but I can't really understand why.
Thank you for your time!
答案1
得分: 2
=SUMIFS(D:D, C:C, I4, INDEX(MONTH(A:A), I$2))
- 参数
month(A3:A)没有应用于整个A3:A列范围，与将其写为month(A3)相同。您可能需要将其包装在arrayformula / index中，以便应用于整个列范围。否则，您将收到Array arguments to SUMIFS are of different size.错误。
英文:
<!-- language-all: js -->
You may try this in Sheets
=sumifs(D:D,C:C,I4,index(month(A:A)),I$2)
- the parameter
month(A3:A)is not applied across the wholeA3:Acolumn_range & is same as writing it asmonth(A3). You may need to wrap it in anarrayformula / indexso that it is applied to the whole column_range. Otherwise you will be prompted withArray arguments to SUMIFS are of different size.error
答案2
得分: 1
Sure, here are the translated parts:
- 使用真实日期来比较日期列，以获取条件月份的第一个日期和最后一个日期。尝试使用以下公式：
=SUMIFS($D$3:$D,$C$3:$C,$F4,$A$3:$A,">="&DATE(2023,$G$3,1),$A$3:$A,"<="&EOMONTH(DATE(2023,$G$3,1),0))
- 另一个不错的选择是使用
QUERY()函数。这个公式可以在不手动添加其他公式的情况下获得结果。尝试使用以下公式：
=QUERY(HSTACK(INDEX(MONTH(A3:A)),C3:D),"select Col2,sum(Col3) where Col1=" & G3 & " group by Col2 label Col2 'Tasks', sum(Col3) 'Total')
如果需要更多帮助，请告诉我。
英文:
Use a true date to compare date columns for 1st date and last date of criteria month. Try-
=SUMIFS($D$3:$D,$C$3:$C,$F4,$A$3:$A,">="&DATE(2023,$G$3,1),$A$3:$A,"<="&EOMONTH(DATE(2023,$G$3,1),0))
Another good choice could be using QUERY() function. This formula will give results without manually putting other formulas. Try-
=QUERY(HSTACK(INDEX(MONTH(A3:A)),C3:D),
"select Col2,sum(Col3)
where Col1=" & G3 & " group by Col2 label Col2 'Tasks', sum(Col3) 'Total'")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论