How to query(traverse) along a wide format table?

user_620061 0
Viewed 57

I have a table like that.

Table example

I can't get my head around how to traverse(query selectively) through the countries and output data per date. Sometimes I need one country, sometimes 3 or 5.
I just need to have a separate table where I'll have something like that for later processing (charts and other calculations). enter image description here

Usually, I'd use something like 

=QUERY(A1:100, "SELECT a column WHERE a row ='Name of a country'")

or

=TRANSPOSE(QUERY(A1:100, "Select * WHERE A = 'Australia'"))

But it looks like this logic doesn't work here. Any ideas?

Here is a dummy sheet

P.S. The second Cumulative column it's just an example of calculations that I'll need to do later with each country numbers. I'll just add =iferror(B3+C2,"0") Although if you have any better ideas on how to optimise that, I'll be happy to hear it.

google-sheets-query google-query-language google-sheets-formula google-sheets
1 Answers

try:

=QUERY(TRANSPOSE(QUERY(TRANSPOSE(Sheet4!A:AQ), 
 "where Col1 matches 'Date|Australia|India'", 0)), 
 "where Col1 >= "&DATEVALUE("2020-1-23")&" 
    and Col1 <= "&DATEVALUE("2020-1-30"), 1)

0

and the cumulatives (running totals) can be done like:

=ARRAYFORMULA(IF(O3:O="",,SUMIF(ROW(O3:O), "<="&ROW(O3:O), O3:O)))

0

user_56326290
Related