2

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.

player0
  • 124,011
  • 12
  • 67
  • 124
Alex Reds
  • 443
  • 2
  • 6
  • 15

1 Answers1

3

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

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    further query examples can be found in **j.mp/covid-19spreadsheet/** – player0 Mar 24 '20 at 09:32
  • This looks neat. And I think I almost understand it. You did nested queries but I don't understand the ampersands. could you elaborate a little bit? Id like to learn to use this formula myself. – Alex Reds Mar 25 '20 at 19:52
  • Cumulative formula wise. I think I know what you did there. IF a cell in column O equals blank, do nothin if true, if false then sum up cells in O column IF its row number is less or equal to the row number. Is that á correct transcriptions of the formula? I don’t get how the last bit work tho. Could you elaborate.please on that as well. – Alex Reds Mar 25 '20 at 20:07
  • And yes I saw your covid19 sheets. amazing. So much to learn. thanks for sharing it. – Alex Reds Mar 25 '20 at 20:08
  • 1
    cumulative transcriptions: sum range O3:O (third sumif parameter) if row number is equal or smaller (second sumif parameter) than row number of current row (first sumif parameter) an do this only IF O3:O is not empty otherwise output blank cell – player0 Mar 25 '20 at 23:03
  • in your dummy sheet there is no United Kingdom but check Sri Lanka: https://i.stack.imgur.com/ngDYJ.png – player0 Mar 25 '20 at 23:08
  • 1
    ampersands... those input dates are not actual dates but numbers visually converted into dates thats why we dont use **date** parameter inside query string as usually. insted we feed the query with numeric representation of a date eg DATEVALUE so in other words we briefly interupt query string syntax (second parameter of query) to inserdatevalue furmula hence those ampersands – player0 Mar 25 '20 at 23:16
  • Gotcha on the ampersands. Thank you – Alex Reds Mar 26 '20 at 02:57
  • here's mine: https://docs.google.com/spreadsheets/d/1NsFnxpJjm2Raada1VJ6h1f-wGZr2V9NboMrrVi8i_g8/edit#gid=2049716153 – player0 Mar 26 '20 at 03:07
  • replied. It's weird but it doesn't work for me in another sheet o_O – Alex Reds Mar 26 '20 at 04:48