How to aggregate rows with the same key in Google Sheets

Question

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

Answer 1

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.