英文:
Sum by Month & Year: Query in Google Sheets and how to ignore blank cells?
问题
Sum by Month & Year: 在Google Sheets中查询
S1中的日期格式为 mm/dd/yy
尝试对 ‘S1’!F 中的值求和
我找到了这篇帖子 ，其中contributorpw的方法非常合适。但是，我不知道如何排除col A中的空单元格。
我知道使用QUERY可以这样做：“…where A is not null”，但是使用以下代码出现了错误消息：
=QUERY(ARRAYFORMULA({TEXT('S1'!A:A,"YYYY-MM"),'S1'!F:F}),"select Col1,sum(Col2) where A is not null group by Col1")
而且这仍然返回了‘S1’!A中的空单元格的值：
=QUERY(ARRAYFORMULA({TEXT('S1'!A:A,"YYYY-MM"),'S1'!F:F}),"select Col1,sum(Col2) where Col1 is not null group by Col1")
英文:
Sum by Month & Year: Query in Google Sheets
The date format in S1 is mm/dd/yy
Attempting to sum the values in ‘S1’!F
I found this post and contributorpw’s method was spot on. However, I don’t know how to exclude blank cells in col A.
I know with QUERY: “…where A is not null” but got error message with:
=QUERY(ARRAYFORMULA({TEXT('S1'!A:A,"YYYY-MM"),'S1'!F:F}),"select Col1,sum(Col2) where A is not null group by Col1")
And this still returned values for blank cells in ‘S1’!A:
=QUERY(ARRAYFORMULA({TEXT('S1'!A:A,"YYYY-MM"),'S1'!F:F}),"select Col1,sum(Col2) where Col1 is not null group by Col1")
答案1
得分: 1
以下是翻译好的部分：
你可以尝试：
=query({index(if(len('S1'!A2:A),eomonth('S1'!A2:A,),)),'S1'!F2:F},"select Col1,sum(Col2) Where Col1 is not null group by Col1 label sum(Col2) '' format Col1 'mmm-YY'")
英文:
You may try:
=query({index(if(len('S1'!A2:A),eomonth('S1'!A2:A,),)),'S1'!F2:F},"select Col1,sum(Col2) Where Col1 is not null group by Col1 label sum(Col2) '' format Col1 'mmm-YY'")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论