0

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")
wejoey
  • 216
  • 1
  • 3
  • 14
Chele
  • 1
  • 2

1 Answers1

0

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'")

enter image description here

rockinfreakshow
  • 15,077
  • 3
  • 12
  • 19