0

I have a table of dates with names that I would like to aggregate. As time passes, more dates and more names can be added, so I'd like this solution to auto-populate itself. Basically,

Abe Bob Charlie
5/12 2 1 x
5/12 x
5/13 1 1 1
5/13 1 1

should become

A B C
5/12 1 2 1
5/13 1 2 2

I tried COUNTA(QUERY({Input!$A$1:$H$10}, "SELECT Col"&Column(B$1) - Column($A$1) + 1&" WHERE Col1 = '"&$A2&"'")) - This works, but it doesn't allow me to autopopulate the rest of the table.

I think I need to use ArrayFormula somehow, but from what I've read, it's only able to iterate over the input to the query and not the values in the various columns. I tried ARRAYFORMULA(COUNTA(QUERY({A1:H1; ARRAYFORMULA(VLOOKUP($A$2, Input!$A$1:$H$10, COLUMN(Input!$A$1:$H$1), 0))}, "SELECT Col2 WHERE Col1 = '"&$A$2:$A$4&"'"))), but it doesn't iterate as desired (over $A$2:$A$4).

I'm stuck on how to achieve this.

I have a sample sheet here

0

Try this:

=arrayformula( 
  query( 
    { Input!A1:Z }, 
    "select Col1, 
     count(Col" & 
       textjoin( 
         "), count(Col", 
         true, 
        if( len(Input!B1:Z1), column(Input!B1:Z1), "" ) 
      ) & ") 
     where Col1 is not null 
     group by Col1  
     label count(Col" & 
       textjoin( 
         "', count(Col", 
         true, 
         if( len(Input!B1:Z1), column(Input!B1:Z1) & ") '" & Input!B1:Z1, "" ) 
       ) & "' ", 
    1 
  ) 
)

The query() function only accepts one data type per column. Most of your data is numbers formatted as Format > Number > Plain text. The counts will be correct only as long all the data is of the same type — in this case, text.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.