Home>

I'm trying to get the total value using QUERY function in Google spreadsheet.

By referring to this page, you can now understand the year and month.
https://qiita.com/sakaimo/items/4a501b12a07718085e6d

= QUERY (E: F, "
select E, sum (F)
  where E is not null
  group by E
  order by E desc
  label E 'Year month', sum (F) 'Total' "
  , true)


However, the D column value is output to the E column for the year and month.
Can Inarrow this by specifying column D in QUERY?

I tried various things, but I couldn't realize it because an error occurred

I changed the
E part as follows ... everything would result in an error
year (E)&month (E) +1
year (E) "&" month (E) +1
format (E, 'yyyy/mm')
DATE (YEAR (E), MONTH (E), 1)

I tried to find it using a search engine, but I could only find examples for the year and the month.

  • Answer # 1

    I was able to do it by the following method.
    Actually I wanted to make it one cell for "Year and Month", but for the time being, the purpose was fulfilled, so ...

    = QUERY (D: F, "
    select year (D), month (D) + 1, sum (F)
    where D is not null
    group by year (D), month (D) +1
    order by year (D) DESC, month (D) +1 DESC
    label year (D) 'year', month (D) +1 'month', sum (F) 'sum'
    ", true)

Related articles