Search code examples
google-sheetstransposearray-formulasgoogle-sheets-querygoogle-query-language

Query particular row + remove X columns + and sum the rest in one formula?

I have a CSV file that I'm pulling from a database. It's in an awkward layout so I need to reorganise it and display the result in a separate sheet.

Here is a dummy example of the data structure I get. https://docs.google.com/spreadsheets/d/1sTfjr-rd0vMIeb3qgBaq9SC8felJ1Pb4Vk_fMNXQKQg/edit?usp=sharing

It looks like that. The database grows every day by date and sometimes countries so I need to account to that in my formula. enter image description here

I need to pull data per each country and display it by date. I don't need data from Column A, C and D. And when there are multiple states I need to sum them up in one column.

It should look like this and keep growing downwards. I'm gonna use this table for a graph chart enter image description here

What I've tried so far 

=TRANSPOSE(QUERY(IMPORTRANGE("url_to_a_separate_sheet_where_I_importing_a_row_csv_file", "CSV-source-sheet!A1:500"), "SELECT * WHERE Col2='Germany'"))

This works, kinda. But pulls in unnecessary columns and I can't figure out how to sum countries with multiple states. When I add select sum(*) it gives me a big and long error. I assume it might be because of unnecessary columns that the formula cant sum up and I don't know how to omit them. I'm stuck

I tried offset and skipping no luck. Any ideas?

Solution

  • try:

    =ARRAYFORMULA(TRANSPOSE(QUERY({Sheet2!B:B, Sheet2!E:BE}, 
 "select Col1,"&TEXTJOIN(",", 1, 
 "sum(Col"&ROW(INDIRECT("Sheet2!A2:A"&COUNTA(Sheet2!1:1)-5))&")")&" 
  where Col1 is not null 
  group by Col1 
  label Col1'Date'", 1)))

    0

    spreadsheet demo